[Help] Lookup with Multiple Criteria

Hi!

I need some help with looking up with multiple criteria, the reason for that is I need to use data from a pivot table with some lookup formula that considers 2 values (City and Product) in order to build a 'dynamic dashboard' with tables.

It's quite hard to explain it by text, so I'll do my best using images:
Image 2 (lookup).png

(image 1) The table on the 'dashboard' sheet is supposed to fill up with information from the Pivot Table located on another sheet.
• The B3 data (City) will be input by the user
• The data on 'dashboard' table (C7:E10) needs to automatically filled depending on the B3 and pivot table data
Image 1 (database).png

So.. I tried to use INDEX MATCH, INDEX MATCH-MATCH with no avail

worksheet or Tab name : dashboard

Cell C7 formula , Drag down and across

Formula:

Please Login or Register  to view this content.

You'll probably get some good answers from people who use Pivot Tables a lot, but here's a solution without using the Pivot Table:

=SUMIFS(Sales_Data[Quantity],Sales_Data[[City]:[City]],$B$3,Sales_Data[[Category]:[Category]],$B7)

You can drag this across and down

Hey Glenn,

Maybe there’s a way to workaround the raw data in order to achieve what I need (or expect) and I’m not seeing it.

The main issue with the raw data is that it’s filled with ~15 columns and rows that’ll easily reach and surpass 10.000 rows.

The pivot table is helping with sorting, filtering data from highest to lowest products and let’s say, customer satisfaction ratings. So, there’s a lot of information to work with. As I said, maybe there’s a way to work with the raw data, but today I’m trying to deal with the pivot table, if that’s even possible.

Try this:

C7=GETPIVOTDATA("Sum of Quantity",'pivot table'!$A$3,"City",$B$3,"Category",$B7)

D7=GETPIVOTDATA("Sum of UnitPrice",'pivot table'!$A$3,"City",$B$3,"Category",$B7)

E7=GETPIVOTDATA("Sum of TotalPrice",'pivot table'!$A$3,"City",$B$3,"Category",$B7)

Then copy down to other rows. If a result might not be in the PivotTable then you can wrap it in IFERROR to avoid errors showing.

PS Sum of Unit Price seems an unusual quantity to measure.

@Sakrammentum You're Welcome. Glad to help . Thank You for the feedback and rep.

POST#5 The attachment provided is invalid, please upload again