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
We then create drop down menus for our Sales Rep column and another one for our Units/Sales/Avg Sale column names
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.