Finding latest match in range

  • Thread starter Thread starter IGM
  • Start date Start date
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
 
=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.
 
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)
 
Sure did. Match always matches the last in a list. Upset me when I tried NOT
to do it. :-)
 
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
 
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)
Click to expand...
 
Back
Top