Lookup

[puiuluipui]

Hi, i need to search the content of C10 in range A1:L1, and to display next
cell content.
Ex:
A1 B1 C1 D1 E1 F1 G1 H1
1 222 2 333 3 444 5 555.....
C10= 3
The formula to display 444
If C10=1, formula to display 222
Something like (A1:L1,C10 content +1)
Can this be done?
Thanks!

 

[Joel]

You are looking for the column that is twice the input

input column
1 2
2 4
3 6
4 8


=OFFSET(A1,0,2*C10-1,1,1)

 

[Jacob Skaria]

Try
=INDEX($A$1:$L$1,MATCH(C10,$A$1:$L$1,0)+1)

If this post helps click Yes

 

[Mike H]

Hi,

= OFFSET(A1,0,MATCH(C10,A1:L1,0))

Mike

 

[puiuluipui]

Hi Joel, it,s working if the content of C10 is entered manually. But the
content of C10 is given by a formula. In my table, C10 is in fact H8.
Formula in H8 is given by this formula : =HLOOKUP(H7,rate!E7:AC8,2,FALSE)
And this is your formula adjusted to my table : =OFFSET(rate!E8,0,2*H8-1,1,1)
Can your code ignore the formula in H8 and see only what is displayed?
Thanks!

 

[puiuluipui]

Works great!
Thanks!

Try
=INDEX($A$1:$L$1,MATCH(C10,$A$1:$L$1,0)+1)

If this post helps click Yes

 

[puiuluipui]

Works great!
Thanks!

Hi,

= OFFSET(A1,0,MATCH(C10,A1:L1,0))

Mike

 

[Mike H]

Your welcome and thanks for the feedback

Works great!
Thanks!

 

[Bernd P]

Hello,

If I understand this correctly, we need to search for the C10 value in
A1, C1, E1, G1, ... and then we would like to get the next value right
to the found one returned.

I suggest to introduce a helper row:

Select row 2 and array-enter:
=INDEX(1:1,1,COLUMN(1:1)*2-1)

Now enter normally into C1:
=INDEX(1:1,MATCH(C10,2:2,0)*2)

Regards,
Bernd

 

[Bernd P]

Hello,

Enter into C10:
=--H8

HTH,
Bernd

 

[Bernd P]

Hello,

Test Joel's and Jacob's solution with
1 2 2 333 3 444 5 555 ...
and put 2 into C10.

Do you really expect 2 as a result or would you need 222?

Regards,
Bernd