vlookup - Col_index_num

  • Thread starter Thread starter xyz
  • Start date Start date
X

xyz

How could i automatically change the col_index_num on a vlookup.

assuming that i got two tab : sheet 1 and sheet 2
sheet 1, is where I am doing the search
sheet 2, is where my data

sheet 2 contain

J 5
F 6
M 7

from the table above, if i do a vlookup("J",sheet1!A:B,2,false) I would get
a result of "5"

let say I need to insert a column in sheet 2, that would look like this

J m 5
F n 6
M o 7

the result on table 1 vlookup("J",sheet1!A:C,2,false) will now be "m"
since I inserted a column, though the table_array automatically adjust, but
the col_index_num remain the same, is there a way to have the col_index_num
to automatically adjust too?

Thanks
 
=VLOOKUP("J",Sheet1!A:C,COLUMN(Sheet1!C:C),FALSE)

--
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)
 
Thanks Bob,

I got a follow up question,
what if my search column do not start at column "A",
say it is on column B

with the VLOOKUP("J",Sheet1!B:C,COLUMN(Sheet1!C:C),FALSE)
I got a "REF#" error on this, is there still a way to do it
If it is not on column "A" ?

Thanks again
 
You still use Column(Sheet1!B:B) to start with, because it is that number 2
that you want to use dynamically, nothing to do with the columns in the
lookup table.

--
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)
 
Hi Bob,

Thank you for quick reply,
if i use column(Sheet1!B:B) that fix my column count to "2",
so when i insert a column in the source data the column count does not change

orig data (prior to inserting a column)
J 5
F 6
M 7
vlookup("J",Sheet1!A:B,COLUMN(B:B),FALSE) = 5

inserting column from orig data
J m 5
F n 6
M o 7
the vlookup formula will automatically change into this
vlookup("J",Sheet1!A:C,COLUMN(B:B),FALSE) = m
the table_array did adjust, but the col_index_num remain (B:B) which
is equal to "2", but since I inserted a new column, it should now be "3"

Thanks again
 
correction, since I have the data now starting at column B instead of column A

orig data, column B & C, (prior to inserting a column)
J 5
F 6
M 7
vlookup("J",Sheet1!B:C,COLUMN(B:B),FALSE) = 5

inserting column from orig data, column will now be B, C & D
J m 5
F n 6
M o 7
the vlookup formula will automatically change into this
vlookup("J",Sheet1!B:D,COLUMN(B:B),FALSE) = m,
and i need to keep the result of "5"
the table_array did adjust, but the col_index_num remain (B:B) which
is equal to "2", but since I inserted a new column, it should now be "3"

Thanks
 
xyz,

If you use "COLUMN(B:B)" and you insert a column between B & C, then
"COLUMN(B:B)" will not adjust.

If you change "COLUMN(B:B)" to "COLUMN(C:C)", then "COLUMN(C:C)" should
automatically change to "COLUMN(D:D)" when you insert a column between B &
C.

But you might need to do something like this in order to get it to work:
"COLUMN(C:C)-1"

HTH,

Conan
 
Thanks Conan,

did try it, but still didn't work,
i will try to experiment on it further again tomorrow

Thanks again.
 
Wait a minute!!! I think I see what the problem is!!!

This VLOOKUP() formula is on Sheet2, correct? We are looking up data from
Sheet1, correct?

"COLUMN(C:C)" refers to column C on Sheet2, if you insert a column on
Sheet1, it is not going to have any affect on this part of the formula.

Change "COLUMN(C:C)" to "COLUMN(Sheet1!C:C)". See if that works.

Also, keep in mind that you might have to add "-1" or "-2" to the end of
"COLUMN(Sheet1!C:C)" to get it to return the value you are looking up.

Now, if that STILL doesn't work, then assign a name to Sheet1!C:C (create a
named range). Then you could change "COLUMN(Sheet1!C:C)" to
"COLUMN(NameYouGaveToColumnCOnSheet1)"

HTH,

Conan
 
My original formula referred to Sheet1.

--
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)
 
looky there, it did. Some how it got lost in the translation.

Thanks again for all of your help, Bob.

Conan
 
Back
Top