From all reference methods in excel, this is still my favorite, for many reasons. It’s dynamic, multi-use and relatively simple to use. The new X-lookup will only work with 365 subscription (or latest versions) so, learn INDEX / MATCH now and enjoy the advantages.
STEP 1
Understand Your Data
Let’s assume you have a list of products in Column A and their prices in Column B. You want to find the price of a specific product.
STEP 2
Use The INDEX Function
Normally, the INDEX function looks like this:
= INDEX (array, row_number, [column_number])
For this example, if your prices are in B2:B10, it would be:
= INDEX (B2 : B10, row_number)
The row number is what we’ll get using MATCH
STEP 3
Time for the Match function
Use The MATCH Function
The MATCH function returns the position of a value within an array:
= MATCH (lookup_value, lookup_array, [match_type])
If you’re searching for the price of “Apple”, located in A2:A10, your MATCH function would be
= MATCH (“Apple”, A2 : A10, 0)
STEP 4
Combine
=INDEX(B2:B10, MATCH(“Apple”, A2:A10, 0))
This will return the price of “Apple” from Column B.
Step 5
Make it Dynamic
Replace “Apple” with a cell reference where you’ll input the product name you’re looking for, e.g.,
=INDEX(B2:B10, MATCH(D1, A2:A10, 0))
To go even further, watch how Carl Seidman teaches this cool trick to make INDEX function become even more dynamic: check it out his video on linkedin