Vlookup Function
 

I am using the vlookup function.

How can I use the above function, so that I don't have to change the column
reference (col_index_num), when the relevant lookup column is moved within
the table (table_array)?

Vlookup Function
 

Replace it with an index/match. The indexed col (return col) will auto-adjust
when you move it.

Example:
If you have in B2: =VLOOKUP(A2,Sheet2!A:D,3,0)
replace it with this equivalent index/match:
=INDEX(Sheet2!C:C,MATCH(A2,Sheet2!A:A,0))

When you move (cut n paste) Sheet2!C:C to say Sheet2!E:E
the formula will auto-adjust to stay in sync, viz.:
=INDEX(Sheet2!E:E,MATCH(A2,Sheet2!A:A,0))

while the vlookup stays unadjusted at:
=VLOOKUP(A2,Sheet2!A:D,3,0)
--
Max
Singapore
http://savefile.com/projects/236895
Downloads:20,500 Files:365 Subscribers:65
xdemechanik
---
"nc" wrote:
I am using the vlookup function.

How can I use the above function, so that I don't have to change the column
reference (col_index_num), when the relevant lookup column is moved within
the table (table_array)?

Vlookup Function
 

Thanks.

What about if I want the vlookup to pick the column based on the field name?



"Max" wrote:

Replace it with an index/match. The indexed col (return col) will auto-adjust
when you move it.

Example:
If you have in B2: =VLOOKUP(A2,Sheet2!A:D,3,0)
replace it with this equivalent index/match:
=INDEX(Sheet2!C:C,MATCH(A2,Sheet2!A:A,0))

When you move (cut n paste) Sheet2!C:C to say Sheet2!E:E
the formula will auto-adjust to stay in sync, viz.:
=INDEX(Sheet2!E:E,MATCH(A2,Sheet2!A:A,0))

while the vlookup stays unadjusted at:
=VLOOKUP(A2,Sheet2!A:D,3,0)
--
Max
Singapore
http://savefile.com/projects/236895
Downloads:20,500 Files:365 Subscribers:65
xdemechanik
---
"nc" wrote:
I am using the vlookup function.

How can I use the above function, so that I don't have to change the column
reference (col_index_num), when the relevant lookup column is moved within
the table (table_array)?

Vlookup Function
 

What about if I want the vlookup to pick the column based on the field name?
That's a new query. You could try using a MATCH expression to return the
col_index_num within the vlookup.
--
Max
Singapore
http://savefile.com/projects/236895
Downloads:20,500 Files:365 Subscribers:65
xdemechanik
---