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.

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

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: 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.

DOWNLOAD WORKBOOK

Index-Match - Data Validation

HELPFUL RESOURCE:

728x90

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

Share on Google+

Google+

Related Posts

Excel´s TEXT Function The TEXT function in Excel allows you to convert a numeric value to a specific format by using special format strings. If you have a date and want to show just the month or if you have a large number and want to show it in a thousands format , then the TEXT function is your sa...
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) ...
SUMIFS Function: Introduction The SUMIFS function allows you to Sum multiple criteria. For example, you can select one Sales Rep from a list of Sales Reps and select one Region from a list of Regions and return the Sum of those arguments from a Sales list.  See how easy it is... DOWNLOAD WORKBOOK ...
INDIRECT Function for Dependent Dropdown Lists in ... The INDIRECT function is really cool as it opens up a lot of interesting combinations in Excel. We will use the power of the INDIRECT function right now on creating Dependent Dropdown Lists. DOWNLOAD EXCEL WORKBOOK Let us go through the steps in detail:   STEP...