Lookup data in a variable table & retrieve data from a pivot table

[Shawna]

I am trying to lookup data from two concatenated colums and retrieve data
from a pivot table where the column & row matches the concatenation, return
the max value in the pivot table. Below is my example data:

Pivot Table
Max of Value
Column Row Total
AB_N AB
AL 1.25
AR 1.15
AZ 1.62
BC
CA 1.25
CO 1.95
AL_N AB 1.95
AL 3
AR 1.75
AZ 1.65
AL_S AB 1.95
AL 3
AR 1.75
AZ 1.65

I want to retreive the max value where the concatenation of the below table
matches matches the column and row in the below table.

Table
O Region D State Concatenation
AR_N FL AR_NFL
PA_E GA PA_EGA
VA_E WA VA_EWA
VA_E TX VA_ETX
VA_E FL VA_EFL

Can anyone help?

 

[Max]

Assume the pivot's data rows are in A3:C3 down
In D3: =A3
In D4: =IF(A4="",D3,A4)
Copy D4 down as far as required
This quickly creates the required "filled" helper col (for col A)
which remains dynamic to what's in the pivot's col A

Then, assuming the region & state paired inputs
are listed in F3:G3 down, eg:
AL_N AL
AB_N AZ
AL_S AR
etc

(the concat col can be dropped)

Just place this in H3, normal ENTER, then copy down:
=INDEX($C$3:$C$100,
MATCH(1,INDEX(($D$3:$D$100=F3)*($B$3:$B$100=G3),),0))
to return the required max values from the pivot's col C
Adapt the ranges to suit the max expected extent
--
Max
Singapore
http://savefile.com/projects/236895
Downloads:18,600 Files:362 Subscribers:60
xdemechanik

 

[Shawna]

Thanks Max! This is exactly what I was looking for. I figured I was making
it more complex than it really needed to be. Thanks again!

 

[Max]

Delighted it helped. My pleasure, Shawna.
--
Max
Singapore
http://savefile.com/projects/236895
Downloads:18,600 Files:362 Subscribers:60
xdemechanik