Index/Match Functions to Return Concatenated Response

[Climate Pro]

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

Basically, I have a data set that shows usage, by part (row), by cit
(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 th
formula pull the city and qty based on the highest value in the par
row. So, for example:

Part A Cell Formula (find Part A from above sheet, find highest valu
in Part A row, reference the city from that row/column, and displa
"City - Qty".

So, for Part A, the formula value would be "HOU - 4" and for Part B i
would be "CHI - 7".

Anyone know how to do this? I have a huge list of parts and about 3
cities and need to find the highest value/city in each part

 

[lhkittle]

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.

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

 

[lhkittle]

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

 

[lhkittle]

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.

 

[Climate Pro]

Thanks Howard - worked well