Back to Excel Tips Index

Most advanced lookups and normal lookups can be accomplished with the combination of two native Excel functions: INDEX and MATCH.

INDEX

The INDEX function by itself is for the most part useless.  The syntax for this function is:

=INDEX(rng_of_value_to_return,row_index,[col_index])

·  rng_of_value_to_return is simply the column or row of the value you want to return.  If you have different fruits in column A and the price of the fruit in column B and you want to find the price of apples, then the rng_of_value_to_return is B:B.

·  row_index is simply the row that the rng_of_value_to_return resides.  So if the price of Apples is in row 2 then the row_index is 2.

·  [col_index] is an optional argument that determines which column the data resides.

INDEX simply returns the value in the row and/or column that you specify.  (One thing special about the INDEX function is that it returns the cell address as well, but it's behind the scenes and not visible...this address can be used in formulas.)

MATCH

The MATCH function works much like VLOOKUP and HLOOKUP.  So if you are familiar with these other lookup functions then you'll probably understand MATCH fairly easily.  However, instead of returning a cell value, MATCH returns the row/column number in the range that you specify.

The syntax for this function is:

=MATCH(lookup_value,lookup_range,[match_type])

·  lookup_value is simply the value you want to lookup in a list.

·  lookup_range is the column or row where your lookup_value resides.

·  [match_type] is the type of match (approximate or exact)

INDEX/MATCH Combination

The trick to the INDEX/MATCH combination, if performing a vertical lookup, is to use the MATCH function to determine the row_index in the INDEX function.  The syntax for the combination is:

=INDEX(Rng_of_value_to_return,MATCH(lookup_value,lookup_range,[match_type]))

This would look something like:

=INDEX(Sheet1!A:A,MATCH(C1,Sheet1!B:B,0))

If you read it out loud it would read, "Return the exact value in Sheet1 column A from the row in Sheet1 column B that matches the value in C1."