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