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.


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&”]”),

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


We need to lookup our Sales Rep within the Sales Rep column table: MATCH(G14,Table1[SALES REP],0))

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

Download the workbook below to practice this method.


Index-Match - Data Validation



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


Pin on Pinterest

Share on Facebook

Tweet about this on Twitter

Share on LinkedIn

Share on Google+

Related Posts

Advanced SUMPRODUCT Function: Conditional Date If you want to find out the total sales for a particular month, then the SUMPRODUCT function is your answer.  You can create a criteria for a specific date range, a particular month or a year.In the example below I show you how to use the SUMPRODUCT function to sum up the tot...
Top Excel Formulas & Function Examples To Get... Below you will find many Excel formula examples for key functions like VLOOKUP, INDEX, MATCH, IF, SUMPRODUCT, AVERAGE, SUBTOTAL, OFFSET, LOOKUP, ROUND, COUNT, SUMIFS, ARRAY, FIND, TEXT, and many more.Click on any Excel formula link below and it will take you to the free e...
Sumproduct & Weighted Averages A quick way to calculate the weighted average of two lists of data is to use the Sumproduct formula.  A weighted average can be used to determine the average salary of employees, the average grade of an exam or the average selling price of a company´s stock list, as can been seen...
Consolidate with 3D Formulas in Excel 3D Formulas or References in Excel are a great way to consolidate data from multiple sheets.3D Formulas reference several worksheets that have the same structure which allows you to consolidate by using the SUM function.Formula breakdown:SUM(Sheet1:Sheet4!A1)...