Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Finding latest match in range
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
=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
Hi,
Assumes your data are in columns A&B =LOOKUP(2,1/(A1:A20="ABC"),B1:B20) Mike "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
Try the following array formula. =IF(COUNTIF(A1:A10,"abc")=0,NA(),OFFSET(A1,MAX((A1 :A10="abc")*ROW(A1:A10))-1,1,1,1)) where A1:A10 is the list containing the alpha values. Note that this is an array formula, so you *must* press CTRL SHIFT ENTER rather than just ENTER when you first enter the formula and whenever you edit it later. If you do this properly, Excel will display the formula enclosed in curly braces { }. See http://www.cpearson.com/Excel/arrayformulas.aspx for more info about array formulas. Cordially, Chip Pearson Microsoft Most Valuable Professional Excel Product Group Pearson Software Consulting, LLC www.cpearson.com (email on web site) On Wed, 15 Oct 2008 13:16:00 -0700, 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 |
#5
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 |
#6
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 |
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Finding latest match in range
One way is to put this in a REGULAR macro module. Assuming col X type
=fl("abc") Function fl(mv) fl = Columns("X").Find(What:=mv, LookIn:=xlValues, _ LookAt:=xlWhole, SearchOrder:=xlByRows, _ SearchDirection:=xlPrevious, MatchCase:=False, _ SearchFormat:=False).Offset(, 1) End Function -- Don Guillett Microsoft MVP Excel SalesAid Software "IGM" wrote in message ... 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 |
#8
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 |
#9
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Finding latest match in range
Thanks Chip ... works perfectly!
"Chip Pearson" wrote: Try the following array formula. =IF(COUNTIF(A1:A10,"abc")=0,NA(),OFFSET(A1,MAX((A1 :A10="abc")*ROW(A1:A10))-1,1,1,1)) where A1:A10 is the list containing the alpha values. Note that this is an array formula, so you *must* press CTRL SHIFT ENTER rather than just ENTER when you first enter the formula and whenever you edit it later. If you do this properly, Excel will display the formula enclosed in curly braces { }. See http://www.cpearson.com/Excel/arrayformulas.aspx for more info about array formulas. Cordially, Chip Pearson Microsoft Most Valuable Professional Excel Product Group Pearson Software Consulting, LLC www.cpearson.com (email on web site) On Wed, 15 Oct 2008 13:16:00 -0700, 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 |
#10
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Finding latest match in range
Wow--where does one learn of that syntax for LOOKUP? Works great!
"Mike H" wrote: Hi, Assumes your data are in columns A&B =LOOKUP(2,1/(A1:A20="ABC"),B1:B20) Mike "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) |