Vlookup or Index Match help

[rdecker12]

Hello,

I am working on a project where I need to retrieve a data point from a separate pivot table. The value I am looking for has a unique ID and month that accompanies it. It is a large table that has thousands of unique ID's spanning across 12 months. To give you an example of what I am looking for... say the unique ID would be 40555 from January 1 2020... I need a formula to retrieve the data value from a separate pivot table that is also broken down by Unique ID and month.

I have a feeling this is an INDEX Match formula or Vlookup yet I am not even sure where to begin. Please let me know possible formulas you suggest.

 

[richh]

Hi rdecker12,

You're right - index match is what you can use to accomplish your goal. The solution below uses named ranges for each column in the sheet the pivot table resides in. It does not look specifically at the pivot table; it looks at the columns the pivot table is in.

I don't know what data you're storing in you books, but the solution is lookup up the customers ID and expiration month from one pivot and retrieving the customers' number of orders and their total cost.

=INDEX(custOrderCount, MATCH(1, ($A2=custOrderID)*($B2=custEXPMonth), 0))

=INDEX(custOrderTotal, MATCH(1, ($A2=custOrderID)*($B2=custEXPMonth), 0))

Sorry for the expeditious gif, but just showing how the data is stored and how the columns in the Order Pivot worksheet are named ranges.

 

[rdecker12]

Hi rdecker12,

You're right - index match is what you can use to accomplish your goal. The solution below uses named ranges for each column in the sheet the pivot table resides in. It does not look specifically at the pivot table; it looks at the columns the pivot table is in.

I don't know what data you're storing in you books, but the solution is lookup up the customers ID and expiration month from one pivot and retrieving the customers' number of orders and their total cost.

=INDEX(custOrderCount, MATCH(1, ($A2=custOrderID)*($B2=custEXPMonth), 0))

=INDEX(custOrderTotal, MATCH(1, ($A2=custOrderID)*($B2=custEXPMonth), 0))

Sorry for the expeditious gif, but just showing how the data is stored and how the columns in the Order Pivot worksheet are named ranges.

View attachment 43485

Awesome, thank you very much!!