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

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

wilchong - 15 May 2008 05:23 GMT

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

Reply to this Message

T. Valko - 15 May 2008 05:55 GMT

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)

Signature

Biff
Microsoft Excel MVP

>I have a minor Excel formula problem.
>
[quoted text clipped - 18 lines]
> Please advice and thanks,
> Wilchong

Reply to this Message

wilchong - 16 May 2008 04:43 GMT

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...
>
[quoted text clipped - 15 lines]
>> Please advice and thanks,
>> Wilchong

Reply to this Message

T. Valko - 16 May 2008 04:57 GMT

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!

Signature

Biff
Microsoft Excel MVP

> Hello T. Valko,
> Many thanks for your advice. I am thinking if I want the excel formula
[quoted text clipped - 31 lines]
>>> Please advice and thanks,
>>> Wilchong

Reply to this Message