What does it do?

Searches the row position of a value/text in one column (using the MATCH function) and returns the value/text in the same row position from another column to the left or right (using the INDEX function)

Formula breakdown:

=INDEX(arrayMATCH(lookup_value, lookup_array, [match_type])

What it means:

=INDEX(return the value/textMATCH(from the row position of this value/text))


We can use the INDEX-MATCH formula and combine it with Data Validation drop down menus to return a value based on 2 criteria.

This is a little advanced so you will need to drop what you are doing and really focus.  Let’s go…

First we need to convert our data into an Excel Table by pressing Ctrl+T

See tutorial on how to convert to an Excel Table here

We then create drop down menus for our Sales Rep column and another one for our Units/Sales/Avg Sale column names

See tutorial on how to insert drop down menus here

Once the above are done we need to create our formula.

Download the workbook below to practice this method.

DOWNLOAD EXCEL WORKBOOK

STEP 1:  We need to nest an INDIRECT function within the INDEX function and reference the Metric cell name (H14) with our Table name (Table1):

=INDEX(INDIRECT(“Table1[“&H14&”]”),

Index Match 2 Criteria

This will give us our dynamic column name within the Excel Table.

 

STEP 2: We need to lookup our Sales Rep within the Sales Rep column table:

=INDEX(INDIRECT(“Table1[“&H14&”]”), 

MATCH(G14,Table1[SALES REP],0))

Index Match 2 Criteria

So by combining these formulas we can choose two criteria (Sales Rep & Metric name) to return the respective value.

Index Match 2 Criteria

 

How to Index Match 2 Criteria with Data Validation in Excel

Index-Match - Data Validation

HELPFUL RESOURCE:

How to Combine VLOOKUP and IFERROR to Replace the #N/A Error in Excel

 

If you like this Excel tip, please share itEmail this to someone

email

Pin on Pinterest

Pinterest

Share on Facebook

Facebook

Tweet about this on Twitter

Twitter

Share on LinkedIn

Linkedin