On Thursday, February 21, 2013 2:30:29 PM UTC-8, wrote:
On Thursday, February 21, 2013 2:24:21 PM UTC-8, wrote:

On Thursday, February 21, 2013 11:18:56 AM UTC-8, Climate Pro wrote:



So, this should be pretty easy for someone to solve.















Basically, I have a data set that shows usage, by part (row), by city







(column). Example:







ATL, CHI, DAL, HOU







Part A 1 2 3 4







Part B 5 7 3 6























I have another sheet that shows just the part and I want to have the







formula pull the city and qty based on the highest value in the part







row. So, for example:















Part A Cell Formula (find Part A from above sheet, find highest value







in Part A row, reference the city from that row/column, and display







"City - Qty".















So, for Part A, the formula value would be "HOU - 4" and for Part B it







would be "CHI - 7".















Anyone know how to do this? I have a huge list of parts and about 30







cities and need to find the highest value/city in each part.







































--







Climate Pro







Try this.







B1 to E1 = has your cities.



A2 to E2 = PART A 1, 2, 3, 4



B2 to E2 = PART B 5, 7, 3, 6







In cells F1 and F2:



=INDEX($B$1:$E$1,MATCH(MAX(B2:E2),B2:E2,0))&" - "&LARGE(B2:E2,1)



=INDEX($B$1:$E$1,MATCH(MAX(B3:E3),B3:E3,0))&" - "&LARGE(B3:E3,1)







Regards,



Howard



Or maybe

=A2&" -- "&INDEX($B$1:$E$1,MATCH(MAX(B3:E3),B3:E3,0))&" - "&LARGE(B3:E3,1)

=A3&" -- "&INDEX($B$1:$E$1,MATCH(MAX(B3:E3),B3:E3,0))&" - "&LARGE(B3:E3,1)



Howard

Drat, a typo in first post!!!

B2 to E2 = PART B 5, 7, 3, 6

Should be:

A3 to E3 = PART B 5, 7, 3, 6

H.