Finding latest match in range

[IGM]

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

 

[Sean Timmons]

=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.

 

[Mike H]

Hi,

Assumes your data are in columns A&B

=LOOKUP(2,1/(A1:A20="ABC"),B1:B20)

Mike

 

[Chip Pearson]

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)

 

[Mike H]

Tested?

=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.

 

[Sean Timmons]

Sure did. Match always matches the last in a list. Upset me when I tried NOT
to do it.

 

[Don Guillett]

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]

Bet you got 30 instead of -20

 

[IGM]

Thanks Chip ... works perfectly!

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)

 

[Steve S]

Wow--where does one learn of that syntax for LOOKUP? Works great!