Home |
Search |
Today's Posts |
#1
|
|||
|
|||
Index/Match Functions to Return Concatenated Response
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. |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Index/Match Functions to Return Concatenated Response
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 |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Index/Match Functions to Return Concatenated Response
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 |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Index/Match Functions to Return Concatenated Response
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. |
#5
|
|||
|
|||
Thanks Howard - worked well.
|
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
INDEX and MATCH functions | Excel Worksheet Functions | |||
Row, Index, Match functions | Excel Discussion (Misc queries) | |||
#n/a response while using the match/index? | Excel Discussion (Misc queries) | |||
Index and Match Functions | Excel Worksheet Functions | |||
How do I use the Match and Index functions to look up a value tha. | Excel Worksheet Functions |