Dynamic searching using VLOOKUP, MATCH and INDEX

Applies to
Microsoft Office Excel 2003

Most Valuable Professional icon This article was written by Ashish Mathur, a Microsoft MVP (Most Valuable Professional). Visit the Microsoft MVP Web site for more information.

In this article, I discuss tools you can use for what I call "dynamic searching" in Excel. What I mean by dynamic searching is the ability to search a row or column for a specific piece of data, and then return that value in another cell. The tools I use are the functions VLOOKUP, MATCH, and INDEX.

Using VLOOKUP

VLOOKUP searches for a value in the left-most column of a table, and then returns a value in the same row from a column you specify in the table. (The V in VLOOKUP stands for vertical.)

To illustrate VLOOKUP, we'll look at a task where we use the data in range B3:C11 — shown in the following picture — to return rates per hour in range F3:F11, for the names listed in range E3:E11. Note that the names in E3:E11 are in a different order than those in B3:B11.

VLOOKUP Example

The simple logic employed here is: Search for a name listed in the range E3:E11, within the range B3:B11. Return the rate per hour for that name, listed in range C3:C11, and place the rate in the range F3:F11.

The formula to accomplish this is:

=VLOOKUP(E3,$B$3:$C$11,2,FALSE)

To understand this formula, consider the syntax of VLOOKUP:

VLOOKUP(lookup_value,table_array,col_index_num,range_lookup)

Now we'll break down the arguments in the syntax for this particular example:

  • lookup_value    Refers to the cell that contains the value you want to look for. Therefore, for cell F3, the lookup_value is E3.
  • table_array     Here, table_array refers to the range that contains both the data you are looking for, and the data you want to return. In our example, range B3:C11 is the list from which rate per hour will be returned.
  • col_index_num    This refers to the column number within the range $B$3:$C$11 that houses the data you want returned. In our example the rate per hour is located in column 2.
  • range_lookup    This value specifies whether you want VLOOKUP to find an exact match or an approximate match. If TRUE or omitted, an approximate or exact match can be returned. For this to work properly, the values in the first column of table_array must be placed in ascending sort order. If FALSE, as in this example, VLOOKUP will find only an exact match. In this case, the values in the first column of table_array do not need to be sorted.

Errors that may result from inserting or deleting data

We now take up certain complications in the formula.

Using ISERROR to deal with a missing name

If we deleted the name Sue P from cell B9, the result in F5 would be #N/A because in cell F5, the formula would not be able to find the name in range B3:C11. To hide the error value, the formula would be:

=IF(ISERROR(VLOOKUP(E3,$B$3:$C$11,2,FALSE)),"",VLOOKUP(E3,$B$3:$C$11,2,FALSE))

The underlying logic here is: If the formula returns an error value, then show a blank in that cell; otherwise, show the rate per hour.

Using MATCH to deal with inserted columns

As you saw in the VLOOKUP formula above, col_index_name is entered as 2, and therefore the formula is not dynamic. That is, if an empty column were inserted between columns B and C of this range, then the formula would return 0 because column 2 would no longer contain any data. Hence, the task now is to make col_index_name dynamic enough to determine the column number of the cell that houses the title Rate per hour. To accomplish this, we use the MATCH function.

The syntax of the MATCH function is:

MATCH(lookup_value,lookup_array,match_type)

Now we'll break down the arguments in the syntax for this particular example:

  • Lookup_value    The reference for the cell that contains Rate per hour. In this case, we can enter $F$1 or specify lookup_value as "Rate per hour".
  • lookup_array    This is the column in which you expect Rate per hour to be — this is dynamic searching. It is worthwhile to note that there should just be one row for lookup_array. That is, do not enter the range $B$1:$D$2; you should enter $B$1:$D$1.
  • match_type    Enter 0 to find an exact match or 1 to find an approximate match. In our case, we enter 0.

The MATCH formula is now: MATCH($F$1,$B$1:$D$1,0) and the result of the formula is 2.

Now if a column is inserted in the range B3:C11, the formula would yield 3. The VLOOKUP formula in cell F3 can now be entered as:

=VLOOKUP(E3,$B$3:$D$11,MATCH($F$1,$B$1:$D$1,0),FALSE)

 Tip   To keep error values from appearing, you can also incorporate the ISERROR function, as explained above.

Gaining flexibility with INDEX

While the VLOOKUP function searches for a value in the left-most column of a table, and then returns a value in the same row from a column you specify in the table, the INDEX function is more dynamic. It can search for a value in any column of a table and return the value from another column in the same row. For example, if the rate per hour is in range A3:A11 instead of C3:C11, then our original VLOOKUP formula would fail, because the value to be searched (Name) is not in the left-most column of the table (range A3:B11).

The solution that INDEX provides is to:

  1. Search for the row (in range A3:B11) in which the name in range D3:D11 appears.
  2. Search for the column in which Rate per hour appears.
  3. Return the value at the intersection of the row and the column.

The syntax of the INDEX function is:

INDEX(array,row_num,column_num)

The syntax of the command can be explained as follows:

  • Array    This corresponds to table_array in the VLOOKUP function. In the example shown in the following picture, the array is A1:B11.
  • Row_num    Refers to the row number of the name, appearing in range D3:D11, in the range B1:B11. As explained earlier, the MATCH function has been used to determine the row number.
  • Column_num    Refers to the column number in range A1:B11 from which we want the data. Again, the MATCH function has been used to determine the column number.

Example of formula using INDEX and MATCH

Hence, the INDEX performs this basic operation: In the range A1:B11, search for the value at the intersection of a particular row and column.

The formula to accomplish this, as shown in the picture above, is:

=INDEX($A$1:$B$11,MATCH(D3,$B$1:$B$11,0),MATCH($E$1,$1:$1,0))
 
 
Applies to:
Excel 2003