How on earth do I look up my value and return its counterpart?! (index match)

We can do this in excel with two formulae combined:

  1. INDEX: returns a value from within a range.
  2. MATCH: returns the postion of an item in a range.

Syntax (Index Match)

individually

=INDEX(column_to_return, row_to_return) #column
=MATCH(look_up_this_value, in_this_range, find_an_exact_match) #row

combined

=INDEX(column_to_return, MATCH(look_up_this_value, in_this_range, find_an_exact_match))

Example (Index Match)

Let’s use an example to illustrate the formula. We want to use our number 3 to find which Pet this corresponds to (Fish)(the black cells).

The Steps

Step One: MATCH

Our first step is to find the row where our number 3 is located. We can do this using the MATCH formula.

look_up_this_value : 3 (lookup_value)

=MATCH(3

in_this_range : We will look for 3 in our number column (lookup_array)

=MATCH(3,look in the number column

find_an_exact_match: 0 specifies an exact match i.e. the value must be exactly 3 (match_type)

=MATCH(3, look in the number column, exact match)

Match Result

As our numbers are in order, from 1-5, it is no surprise the position of the number 3 is in position number 3 of our range.

Step Two: INDEX

Our next step is to use the given position (postion 3) of our value (Number 3) to return our pet. We can do this using the INDEX formula.

column_to_return : we want the Pet from the Pet column (array).

row_to_return : We found this using our MATCH formula above (row_num)

=INDEX(get our value from the pet column, insert MATCH)

We will be returning the 3rd value in our index range. Therefore, we must ensure start our INDEX range from the same row as our MATCH range!

Result (Index Match)

formula

=INDEX(D3:D7,MATCH(F3,B3:B7,0))

output

Index Match

And there you have it, the Pet with the Number 3 is Fish!

Try making the formula more readable by naming ranges