Extracting the data according to the number of cell

[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! Do you think Excel formula has
such function?

Please advice and thanks,
Wilchong

 

[Mike H]

Hi,

I'm not sure i fully understand but how does this look

=INDIRECT("A"&B3+4)

If you now enter a 3 into B3 it will extract the contents of A7.

I'm not sure of the significance of A1 in your question but this extracts
and concatenates A1 and A7

=A1 & INDIRECT("A"&B3+4)

Mike

 

[wilchong via OfficeKB.com]

Hello Mike,
The Excel formula is perfectly working well, however, I don't understand the
contain in the formula. Why we have to put "+4" ?

Many thanks for your advice!
Wilchong

Hi,

I'm not sure i fully understand but how does this look

=INDIRECT("A"&B3+4)

If you now enter a 3 into B3 it will extract the contents of A7.

I'm not sure of the significance of A1 in your question but this extracts
and concatenates A1 and A7

=A1 & INDIRECT("A"&B3+4)

Mike

I have a minor Excel formula problem.

[quoted text clipped - 11 lines]

Please advice and thanks,
Wilchong

 

[Mike H]

Hi,

Consider this

=INDIRECT("A"&B3)

with a number in B3 (3) the formula evaluates as
=(A3)
which would extract the contents of A3 but your data list start in A5 so to
extract the third element of your list we need to add an offset of 4.

In fact no matter what element you want to extract the offset is always 4 so
a 1 in B3 + 4 extract the contents of A5 or the first element of your list.

Hope this helps.

Mike

Hello Mike,
The Excel formula is perfectly working well, however, I don't understand the
contain in the formula. Why we have to put "+4" ?

Many thanks for your advice!
Wilchong

Hi,

I'm not sure i fully understand but how does this look

=INDIRECT("A"&B3+4)

If you now enter a 3 into B3 it will extract the contents of A7.

I'm not sure of the significance of A1 in your question but this extracts
and concatenates A1 and A7

=A1 & INDIRECT("A"&B3+4)

Mike

I have a minor Excel formula problem.

[quoted text clipped - 11 lines]

Please advice and thanks,
Wilchong

 

[wilchong via OfficeKB.com]

Hello Mike
The 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?

Many thank for your time.
Wilchong

Hi,

I'm not sure i fully understand but how does this look

=INDIRECT("A"&B3+4)

If you now enter a 3 into B3 it will extract the contents of A7.

I'm not sure of the significance of A1 in your question but this extracts
and concatenates A1 and A7

=A1 & INDIRECT("A"&B3+4)

Mike

I have a minor Excel formula problem.

[quoted text clipped - 11 lines]

Please advice and thanks,
Wilchong