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.

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

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

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.

Thanks Howard - worked well.