Pinterest Pixel

Index Match 2 Criteria with Data Validation

  What does it do? Searches the row position of a value/text in one column (using the... read more

Download Excel Workbook
John Michaloudis
Posted on

Steps To Follow

Overview

Index Match 2 Criteria with Data Validation | MyExcelOnline Index Match 2 Criteria with Data Validation | MyExcelOnline

 

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

See also  Advanced Sumproduct Function: Sum the Top 3 Sales with an Array Formula

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


Index Match 2 Criteria with Data Validation | MyExcelOnline

Download the workbook below to practice this method.

Download excel workbookIndex-Match-Data-Validation.xlsx

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 with Data Validation

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 with Data Validation

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

Index Match 2 Criteria with Data Validation

 

How to Index Match 2 Criteria with Data Validation in Excel

 

 

If you like this Excel tip, please share it
Index Match 2 Criteria with Data Validation | MyExcelOnline Index Match 2 Criteria with Data Validation | MyExcelOnline

Get Video Training

Advance your Microsoft Excel & Office Skills with the MyExcelOnline Academy!

Dramatically Reduce Repetition, Stress, and Overtime!
Exponentially Increase Your Chances of a Promotion, Pay Raise or New Job!

Learn in as little as 5 minutes a day or on your schedule.

Learn More!

Share to...