Sample data.xlsxI am using below formula to return column header in cell I2 and J2
=INDEX($A$1:$H$1,MATCH(LARGE($A2:$H2,COLUMNS($I2:I 2)),$A2:$H2,0))
Data is..
US MX SAM EU AS CN AF Other
3 1 1 0 1 12 0 0
0 0 0 0 1 0 0 0
0 0 0 0 0 2 0 0
1 0 0 0 0 0 0 0
2 0 0 0 0 0 0 0
0 0 0 0 0 4 0 0
0 0 1 0 0 1 0 0
2 1 1 0 0 0 0 0
1 0 0 0 1 12 0 0
1 0 0 0 0 0 0 0
2 0 0 0 1 12 0 0
Now I had amended that data as follows...
Client Name US MX SAM EU AS CN AF Other
Microsoft 3 1 1 0 1 12 0 0
Nokia 0 0 0 0 1 0 0 0
Apple 0 16 0 9 5 2 0 0
Sun 1 0 0 0 0 0 0 0
Mitsubishi 2 0 0 9 0 0 18 0
Honda 7 10 0 0 7 4 0 45
Pepsico 0 6 1 7 0 1 0 0
Expected result as follows....
Result
Client Name 1st Largest Value 2nd Largest Value
Apple MX EU
Honda Other MX
Pepsico EU MX
I am trying to apply the formula by lookup client name. Foreg: In modified data below, I have just added client name column. I have list of client names in other tab against which i need the data.
How to amend the formula to fit in cells B14, C14, B15, C15, B16 and C16.
Reply With Quote
Bookmarks