| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Find max with a lookup
I have a list of data as follows
A B C 1 Belfast 21 2 South 36 =abs(B1-B2) 3 Laganbank 25 =abs(B1-B3) 4 Balmoral 19 =abs(B1-B4) What I want is to find the max value in column C and return the corresponding name from column A. So in this case, C2 is highest (15) so the value returned would be "South". Note: The numbers in column B are linked to another table and have many decimal places. they are not just typed whole numbers. |
|
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Find max with a lookup
Try the INDEX() MATCH() combination
=INDEX(A1:A4,MATCH(MAX(C1:C4),C1:C4,0)) -- Jacob (MVP - Excel) "sarahphonics" wrote: I have a list of data as follows A B C 1 Belfast 21 2 South 36 =abs(B1-B2) 3 Laganbank 25 =abs(B1-B3) 4 Balmoral 19 =abs(B1-B4) What I want is to find the max value in column C and return the corresponding name from column A. So in this case, C2 is highest (15) so the value returned would be "South". Note: The numbers in column B are linked to another table and have many decimal places. they are not just typed whole numbers. |
|
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Find max with a lookup
worked perfectly. thank you
"Jacob Skaria" wrote: Try the INDEX() MATCH() combination =INDEX(A1:A4,MATCH(MAX(C1:C4),C1:C4,0)) -- Jacob (MVP - Excel) "sarahphonics" wrote: I have a list of data as follows A B C 1 Belfast 21 2 South 36 =abs(B1-B2) 3 Laganbank 25 =abs(B1-B3) 4 Balmoral 19 =abs(B1-B4) What I want is to find the max value in column C and return the corresponding name from column A. So in this case, C2 is highest (15) so the value returned would be "South". Note: The numbers in column B are linked to another table and have many decimal places. they are not just typed whole numbers. |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Lookup, vlookup, find, or what | Excel Discussion (Misc queries) | |||
| Lookup / find value in column which changes | Excel Worksheet Functions | |||
| Lookup...find next | Excel Discussion (Misc queries) | |||
| Can't find the min of a lookup reference | Excel Worksheet Functions | |||
| Lookup Value and find Corresponding Value on another row same column | Excel Discussion (Misc queries) | |||
Linear Mode
