TOP Value in Lookup Table

[Ananth]

Supplier Location Sales
Supplier-A BX1 10
Supplier-A BX2 3
Supplier-A BX3 2
Supplier-A BX4 9
Supplier-B BX5 11
Supplier-B BX6 2
Supplier-B BX7 7
Supplier-B BX8 1
Supplier-C BX9 22
Supplier-C BX10 4
Supplier-C BX11 5
Supplier-C BX12 9
Supplier-C BX13 3



Summary Report
Top Sales Location

Supplier-A
Supplier-B
Supplier-C

I want to see Top sales as 10 and Loacation as BX1 against supplier-A in the
summry repoty. How do I achieve this.

 

[Max]

Assuming data within A2:C14,
col A = suppliers, col B = locations, col C = sales

Assume suppliers below listed in E2:E4

Supplier-A
Supplier-B
Supplier-C

with headers in F1:G1 : Top Sales, Location

Put in F2, array-enter (press CTRL+SHIFT+ENTER):
=MAX(IF($A$2:$A$14=E2,$C$2:$C$14))

Put in G2, array-enter (press CTRL+SHIFT+ENTER):
=INDEX($B$2:$B$14,MATCH(F2,IF($A$2:$A$14=E2,$C$2:$C$14),0))

Select F2:G2, copy down to G4. This should return the required results.