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

Share on Facebook

Tweet about this on Twitter

Share on LinkedIn

Share on Google+

Related Posts

Total Bonus Due With An Array Lookup Formula The LOOKUP function has three arguments, it is a like a simplified VLOOKUP function:What does it do?It looks up a value (lookup_value) in one range (lookup_vector) and returns a value from the same position in a second range (result_vector)Formula breakdown:=LOOKU...
Jump To A Cell Reference Within An Excel Formula When writing, editing or auditing Excel formulas you will come across a scenario where you want to view and access the referenced cells within a formula argument.This is helpful if you want to check how the formula works or to make any changes to the formula.There is ...
INDIRECT Function Using Sheet References What does it do?Returns a reference to a cell, or a range of cells of a sheet.Formula breakdown:=INDIRECT(ref_text, )What it means:=INDIRECT(Return the referenced range, Omit if the reference is an A1 style or enter FALSE if it is a R1C1 style)The ...
Advanced SUMPRODUCT Function: Maximum Sales What makes the SUMPRODUCT function even more powerful is its ability to nest formulas, or in simple terms, add another function within the SUMPRODUCT function.Instead of getting the Total Sales for a region, we can extract the Maximum sales value simply by entering the MAX fu...