| Home |
| Search |
| Today's Posts |
|
|
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Finding latest match in range
=INDEX(A1:B100,MATCH("abc",A1:A100),2)
Will find the last value with "abc" in it and return the matching value in the 2nd column. "IGM" wrote: Hi: I have abc 0 abc 10 abc 20 abc 30 efg 0 efg 10 abc -20 efg 50 How can I express a formula to obtain -20 for "abc" and 50 for "efg" out of this data? My goal is to get the numeric value (B) corresponding to the highest matching row within the specified range. Thanks, IGM |
|
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Finding latest match in range
Tested?
"Sean Timmons" wrote: =INDEX(A1:B100,MATCH("abc",A1:A100),2) Will find the last value with "abc" in it and return the matching value in the 2nd column. "IGM" wrote: Hi: I have abc 0 abc 10 abc 20 abc 30 efg 0 efg 10 abc -20 efg 50 How can I express a formula to obtain -20 for "abc" and 50 for "efg" out of this data? My goal is to get the numeric value (B) corresponding to the highest matching row within the specified range. Thanks, IGM |
|
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Finding latest match in range
Sure did. Match always matches the last in a list. Upset me when I tried NOT
to do it. :-) "Mike H" wrote: Tested? "Sean Timmons" wrote: =INDEX(A1:B100,MATCH("abc",A1:A100),2) Will find the last value with "abc" in it and return the matching value in the 2nd column. "IGM" wrote: Hi: I have abc 0 abc 10 abc 20 abc 30 efg 0 efg 10 abc -20 efg 50 How can I express a formula to obtain -20 for "abc" and 50 for "efg" out of this data? My goal is to get the numeric value (B) corresponding to the highest matching row within the specified range. Thanks, IGM |
|
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Finding latest match in range
Bet you got 30 instead of -20 -- Don Guillett Microsoft MVP Excel SalesAid Software "Sean Timmons" wrote in message ... Sure did. Match always matches the last in a list. Upset me when I tried NOT to do it. :-) "Mike H" wrote: Tested? "Sean Timmons" wrote: =INDEX(A1:B100,MATCH("abc",A1:A100),2) Will find the last value with "abc" in it and return the matching value in the 2nd column. "IGM" wrote: Hi: I have abc 0 abc 10 abc 20 abc 30 efg 0 efg 10 abc -20 efg 50 How can I express a formula to obtain -20 for "abc" and 50 for "efg" out of this data? My goal is to get the numeric value (B) corresponding to the highest matching row within the specified range. Thanks, IGM |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| need to return next match of table, only finding first match...HEL | Excel Worksheet Functions | |||
| Finding Latest Variable | Excel Discussion (Misc queries) | |||
| Finding the latest date represented in a group of cells | Excel Discussion (Misc queries) | |||
| lookup latest entered match | Excel Worksheet Functions | |||
| Finding the Latest Date from Several Dates in Different Columns | Excel Discussion (Misc queries) | |||
Hybrid Mode
