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)

"xyz" wrote in message
...
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

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

"Bob Phillips" wrote:

=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)

"xyz" wrote in message
...
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

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)

"xyz" wrote in message
...
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

"Bob Phillips" wrote:

=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)

"xyz" wrote in message
...
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

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



"Bob Phillips" wrote:

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)

"xyz" wrote in message
...
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

"Bob Phillips" wrote:

=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)

"xyz" wrote in message
...
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

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" wrote:

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



"Bob Phillips" wrote:

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)

"xyz" wrote in message
...
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

"Bob Phillips" wrote:

=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)

"xyz" wrote in message
...
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

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




"xyz" wrote in message
...
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" wrote:

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



"Bob Phillips" wrote:

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)

"xyz" wrote in message
...
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

"Bob Phillips" wrote:

=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)

"xyz" wrote in message
...
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

Thanks Conan,

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

Thanks again.

"Conan Kelly" wrote:

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




"xyz" wrote in message
...
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" wrote:

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



"Bob Phillips" wrote:

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)

"xyz" wrote in message
...
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

"Bob Phillips" wrote:

=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)

"xyz" wrote in message
...
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

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




"xyz" wrote in message
...
Thanks Conan,

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

Thanks again.

"Conan Kelly" wrote:

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




"xyz" wrote in message
...
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" wrote:

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



"Bob Phillips" wrote:

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)

"xyz" wrote in message
...
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

"Bob Phillips" wrote:

=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)

"xyz" wrote in message
...
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

My original formula referred to Sheet1.

--
HTH

Bob

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

"Conan Kelly" wrote in message
...
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




"xyz" wrote in message
...
Thanks Conan,

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

Thanks again.

"Conan Kelly" wrote:

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




"xyz" wrote in message
...
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" wrote:

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



"Bob Phillips" wrote:

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)

"xyz" wrote in message
...
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

"Bob Phillips" wrote:

=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)

"xyz" wrote in message
...
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

looky there, it did. Some how it got lost in the translation.

Thanks again for all of your help, Bob.

Conan




"Bob Phillips" wrote in message
...
My original formula referred to Sheet1.

--
HTH

Bob

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

"Conan Kelly" wrote in message
...
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




"xyz" wrote in message
...
Thanks Conan,

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

Thanks again.

"Conan Kelly" wrote:

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




"xyz" wrote in message
...
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" wrote:

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



"Bob Phillips" wrote:

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)

"xyz" wrote in message
...
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

"Bob Phillips" wrote:

=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)

"xyz" wrote in message
...
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