Extracting the data according the number of cell (at specific range)

[wilchong via OfficeKB.com]

I have a minor Excel formula problem.

In my example has following data, cell A5 shows "apple", A6 shows "salt", A7
shows "sugar" and A8 shows "fish". On another hand, cell B3 is the
"criteria" data showing 3. My question is to form an Excel formula which can
extract the data from cell "A1" and then extract the 3rd data, which is
"sugar" (that is the result I want).

Althought the formula is counting from A1, but the excel formula know the
data after "apple" and "salt" is "sugar" (cell from A1 to A4 is empty),
because "sugar" is located on the 3rd in row!

I know this Excel formula, =INDIRECT("A"&B3), is working very perfect if I
want to scan the data in the WHOLE col A. How about if I have a situation
which the data ONLY located from A3 to A8, how to adjust the formula in order
it can accurately scan the data?

Please advice and thanks,
Wilchong

 

[T. Valko]

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)

 

[wilchong via OfficeKB.com]

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

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

 

[T. Valko]

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

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

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