VLOOKUP

[Lynn]

Have this function

Look up value : A3
Table Array: '01'!Q:S
Column Index Number:2
Range Look Up: False

Want to copy formula into other column
Not change Lookupvalue:A3
Change Table Array:02 and keeping adding for example column D=01. E=02,F=03
not change in table array Q:S
How can I do this?

 

[Stefi]

=VLOOKUP($A$3,OFFSET(INDIRECT(ADDRESS(1,17,1,1,TEXT(COLUMN(D3)-3,"00"))),0,0,100,2),2,FALSE)
^
This work for 100 rows, adjust it to expected max number of rows (or max row
No, 65536 in Excel2003)!


Regards,
Stefi


„Lynn†ezt írta:

 

[Jacob Skaria]

Try
=VLOOKUP($A3,$Q:$S,COLUMN(A2),0)

and copy to the column to the right..Since the array has got only 3 columns
you have only Column Index Number:2 and 3

If this post helps click Yes

 

[Jacob Skaria]

To start from column index number 2 use
=VLOOKUP($A3,$Q:$S,COLUMN(B1),0)

If this post helps click Yes