Finding latest match in range

I

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
 
S

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

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)
 
M

Mike H

Tested?

Sean Timmons said:
=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.
 
S

Sean Timmons

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

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
 
I

IGM

Thanks Chip ... works perfectly!


Chip Pearson said:
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)
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top