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

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

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

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

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

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

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

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

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

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