Excel® 2016 Formulas and Functions (2016)

---

Normally, you don’t use the MATCH() function by itself; you combine it with the INDEX() function. INDEX() returns the value of a cell at the intersection of a row and column inside a reference. Here’s the syntax for INDEX():

INDEX(reference, row_num[, column_num][, area_num])

The idea is that you use MATCH() to get row_num or column_num (depending on how your table is laid out) and then use INDEX() to return the value you need.

To give you the flavor of using these two functions, let’s duplicate our earlier effort of looking up a customer name, given the account number. Figure 9.7 shows the result.

Figure 9.7 A worksheet that uses INDEX() and MATCH() to look up a customer’s name, based on the entered account number.

In particular, notice the new formula in cell B4:

=INDEX(D3:E15, MATCH(B2, D3:D15, 0), 2)

The MATCH() function looks up the value in cell B2 in the range D3:D15. That value is then used as the row_num argument for the INDEX() function. That value is 1 in the example, so the INDEX() function reduces to this:

=INDEX(D3:E15, 1, 2)

This returns the value in the first row and the second column of the range D3:E15.

Looking Up a Value Using Worksheet List Boxes

If you use a worksheet list box or combo box as explained in Chapter 4, the linked cell contains the number of the selected item, not the item itself. Figure 9.8 shows a worksheet with a list box and a drop-down list. The list used by both controls is the range A3:A10. Notice that the linked cells (E3 and E10) display the number of the list selection, not the selection itself.

Figure 9.8 This worksheet uses INDEX() to get the selected item from a list box and a combo box.

To get the selected list item, you can use the INDEX() function with the following modified syntax:

INDEX(list_range, listelection)

For example, to find the item selected from the list box in Figure 9.8, you use the following formula:

=INDEX(A3:A10, E3)

Using Any Column as the Lookup Column

One of the major disadvantages of the VLOOKUP() function is that you must use the table’s leftmost column as the lookup column. (HLOOKUP() suffers from a similar problem: It must use the table’s topmost row as the lookup row.) This isn’t a problem if you remember to structure your lookup table accordingly, but that might not be possible in some cases, particularly if you inherit the data from someone else.

Fortunately, you can use the MATCH() and INDEX() combination to use any table column as the lookup column. For example, consider the parts database shown in Figure 9.9.

Figure 9.9 In this lookup table, the lookup values are in column H, and the value you want to find is in column C.

Column H contains the unique part numbers, so that’s what you want to use as the lookup column. The data you need is the quantity in column C. To accomplish this, you first find the part number (as given by the value in B1) in column H using MATCH():

MATCH(B1, H6:H13, 0)

When you know which row contains the part, you plug this result into an INDEX() function that operates only on the column that contains the data you want (column C):

=INDEX(C6:C13, MATCH(B1, H6:H13, 0))

Creating Row-and-Column Lookups

So far, all the lookups you’ve seen have been one dimensional, meaning that they searched for a lookup value in a single column or row. However, in many situations, you need a two-dimensional approach. This means that you need to look up a value in a column and a value in a row and then return the data value at the intersection of the two. I call this a row-and-column lookup.

You do this by using two MATCH() functions: one to calculate the INDEX() function’s row_num argument, and the other to calculate the INDEX() function’s column_num argument. Figure 9.10 shows an example.

Figure 9.10 To perform a two-dimensional row-and-column lookup, use MATCH() functions to calculate both the row and the column values for the INDEX() function.

The idea here is to use both the part numbers (column H) and the field names (row 6) to return specific values from the parts database.

The part number is entered into cell B1, and getting the corresponding row in the parts table is no different from what you did in the previous section:

MATCH(B1, H7:H14, 0)

The field name is entered into cell B2. Getting the corresponding column number requires the following MATCH() expression:

MATCH(B2, A6:H6, 0)

These provide the INDEX() function’s row_num and column_num arguments (see cell B3):

=INDEX(A7:H14, MATCH(B1, H7:H14, 0), MATCH(B2, A6:H6, 0))

Creating Multiple-Column Lookups

Sometimes it’s not enough to look up a value in a single column. For example, in a list of employee names, you might need to look up both the first name and the last name if they’re in separate fields. One way to handle this is to create a new field that concatenates all the lookup values into a single item. However, it’s possible to do this without going to the trouble of creating a new concatenated field.

The secret is to perform the concatenation within the MATCH() function, as in this generic expression:

MATCH(value1 & value2, array1 & array2, match_type)

Here, value1 and value2 are the lookup values you want to work with, and array1 and array2 are the lookup columns. You can then plug the results into an array formula that uses INDEX() to get the needed data:

{=INDEX(reference, MATCH(value1 & value2, array1 & array2, match_type))}

For example, Figure 9.11 shows a database of employees, with separate fields for the first name, last name, title, and more.

Figure 9.11 To perform a two-column lookup, use MATCH() to find a row based on the concatenated values of two or more columns.

The lookup values are in B1 (first name) and B2 (last name), and the lookup columns are A6:A14 (the First Name field) and B6:B14 (the Last Name field). Here’s the MATCH() function that looks up the required column:

MATCH(B1 & B2, A6:A14 & B6:B14, 0)

We want the specified employee’s title, so the INDEX() function looks in C6:C14 (the Title field). Here’s the array formula in cell B3:

{=INDEX(C6:C14, MATCH(B1 & B2, A6:A14 & B6:B14, 0))}

From Here

To learn how to use data validation to set up an in-cell drop-down list, see “Applying Data-Validation Rules to Cells,” p. 100.

For the specifics of adding option buttons and list boxes to a worksheet, see “Understanding the Worksheet Controls,” p. 105.

For a general discussion of function syntax, see “The Structure of a Function,” p. 130.

To learn about the WEEKDAY() function, see “The WEEKDAY() Function,” p. 214.