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