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