Replace SMALL with LARGE.

=INDEX(A3:A8,LARGE(IF(A3:A8<"",ROW(A3:A8)-MIN(ROW(A3:A8))+1),B3))

Don't forget....array entered!

--
Biff
Microsoft Excel MVP


"wilchong via OfficeKB.com" <u43231@uwe wrote in message
news:843812846ebcd@uwe...
Hello T. Valko,
Many thanks for your advice. I am thinking if I want the excel formula
scan
the data from A8 to A5 (from bottom to top, my early question is from top
to
bottom), and cell B3 is still the "criteria" data showing 3.

Therefore, the result is "Salt". My question is that which parameter
should
I revise in this formular

=INDEX(A3:A8,SMALL(IF(A3:A8<"",ROW(A3:A8)-MIN(ROW(A3:A8))+1),B1)).

Many thanks for your effort!
Wilchong




T. Valko wrote:
Here's one way...

Array entered** :

=INDEX(A1:A8,SMALL(IF(A1:A8<"",ROW(A1:A8)-MIN(ROW(A1:A8))+1),B3))

** array formulas need to be entered using the key combination of
CTRL,SHIFT,ENTER (not just ENTER)

Here's another way *IF* the data entered is *always* TEXT and is *always*
entered in a contiguous range:

=INDEX(A1:A8,MATCH("*",A1:A8,0)+B3-1)

I have a minor Excel formula problem.

[quoted text clipped - 18 lines]
Please advice and thanks,
Wilchong

--
Message posted via OfficeKB.com
http://www.officekb.com/Uwe/Forums.a...l-new/200805/1