| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Lookup
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! |
|
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Lookup
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) "puiuluipui" wrote: 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! |
|
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Lookup
Try
=INDEX($A$1:$L$1,MATCH(C10,$A$1:$L$1,0)+1) If this post helps click Yes --------------- Jacob Skaria "puiuluipui" wrote: 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! |
|
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Lookup
Hi,
= OFFSET(A1,0,MATCH(C10,A1:L1,0)) Mike "puiuluipui" wrote: 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! |
|
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Lookup
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! "Joel" wrote: 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) "puiuluipui" wrote: 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! |
|
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Lookup
Works great!
Thanks! "Jacob Skaria" wrote: Try =INDEX($A$1:$L$1,MATCH(C10,$A$1:$L$1,0)+1) If this post helps click Yes --------------- Jacob Skaria "puiuluipui" wrote: 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! |
|
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Lookup
Works great!
Thanks! "Mike H" wrote: Hi, = OFFSET(A1,0,MATCH(C10,A1:L1,0)) Mike "puiuluipui" wrote: 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! |
|
#8
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Lookup
Your welcome and thanks for the feedback
"puiuluipui" wrote: Works great! Thanks! "Mike H" wrote: Hi, = OFFSET(A1,0,MATCH(C10,A1:L1,0)) Mike "puiuluipui" wrote: 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! |
|
#9
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Lookup
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 |
|
#10
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Lookup
Hello,
Enter into C10: =--H8 HTH, Bernd |
|
#11
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Lookup
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 |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| LOOKUP FUNCTION? (LOOKUP VALUE BEING A TIME RENERATED FROM A FORMU | Excel Discussion (Misc queries) | |||
| Lookup looks to the prior column if zero appears in the lookup col | Excel Discussion (Misc queries) | |||
| Get Cell Address From Lookup (Alternative to Lookup) | Excel Worksheet Functions | |||
| Join 2 Lists - Lookup value in 1 list & use result in 2nd lookup | Excel Worksheet Functions | |||
| Pivot table doing a lookup without using the lookup function? | Excel Discussion (Misc queries) | |||
Linear Mode
