Using Index / Match

November 15, 2023 by
Using Index / Match
Carolina

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.


.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

ace Precedents and Dependents

//


Use these buttons to understand your calculations.  Precedents – where the items in the formulas come from, dependents – where the formula is going to.  Remove arrows will clean everything.


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


Using Index / Match
Carolina November 15, 2023