vlookup update when inserting columns in named tables

[MATHEW BENNETT]

Good evening all,
I have an annoying problem which incurrs lots of additional work, whick I would dearly like a work around for.

I have many vlookup formulae which references a named tables,eg:

A B C D E
1 M1030 200 300 400 500

I have named this list, 'table1', from which I have I have a vlookup function in another worksheet
eg =vlookup($A10,table1,3,0), where $A10 in the other worksheet is 'M1030', which will return the value '300'

This works fine, but if I insert a column in table1, eg between columns B & C, then the vlookup formula still looks
for column 3, which is now a different value.

My question is, is it possible for the vlookup function to automatically know to now look for the value in column 4?

Any help, as always is most appreciated.
Cheers
Mathew

 

[Frank Kabel]

Hi
AFAK not possible in Excel. If your column has a header you could use a
combination of VLOOKUP and MATCH

--
Regards
Frank Kabel
Frankfurt, Germany

Good evening all,
I have an annoying problem which incurrs lots of additional work,

whick I would dearly like a work around for.

I have many vlookup formulae which references a named tables,eg:

A B C D E
1 M1030 200 300 400 500

I have named this list, 'table1', from which I have I have a vlookup function in another worksheet
eg =vlookup($A10,table1,3,0), where $A10 in the other worksheet is

'M1030', which will return the value '300'

This works fine, but if I insert a column in table1, eg between

columns B & C, then the vlookup formula still looks

for column 3, which is now a different value.

My question is, is it possible for the vlookup function to

automatically know to now look for the value in column 4?

 

[Debra Dalgleish]

Instead of typing the column number in the formula, you could use the
MATCH function to return the appropriate column number. For example:

=VLOOKUP($A10,table1,MATCH("Rent",$A$1:$E$1,0),0)

 

[RagDyer]

Might not be a bad idea to *not* limit the search area in Match(), since the
exact number of *eventually* inserted columns is unknown and might force the
sought after column out of the pre-defined range.

=VLOOKUP($A10,table1,MATCH("Rent",$1:$1,0),0)
--


Regards,

RD
--------------------------------------------------------------------
Please keep all correspondence within the Group, so all may benefit!
-------------------------------------------------------------------


Instead of typing the column number in the formula, you could use the
MATCH function to return the appropriate column number. For example:

=VLOOKUP($A10,table1,MATCH("Rent",$A$1:$E$1,0),0)

Good evening all,
I have an annoying problem which incurrs lots of additional work, whick I

would dearly like a work around for.

I have many vlookup formulae which references a named tables,eg:

A B C D E
1 M1030 200 300 400 500

I have named this list, 'table1', from which I have I have a vlookup function in another worksheet
eg =vlookup($A10,table1,3,0), where $A10 in the other worksheet is

'M1030', which will return the value '300'

This works fine, but if I insert a column in table1, eg between columns B

& C, then the vlookup formula still looks

for column 3, which is now a different value.

My question is, is it possible for the vlookup function to automatically

know to now look for the value in column 4?

 

[mathew]

Thanks guys, just the fix I needed. The Match function is one I know, but have never used before. Cheers again,
Mathew

Might not be a bad idea to *not* limit the search area in Match(), since the
exact number of *eventually* inserted columns is unknown and might force the
sought after column out of the pre-defined range.

=VLOOKUP($A10,table1,MATCH("Rent",$1:$1,0),0)
--


Regards,

RD
--------------------------------------------------------------------
Please keep all correspondence within the Group, so all may benefit!
-------------------------------------------------------------------


Instead of typing the column number in the formula, you could use the
MATCH function to return the appropriate column number. For example:

=VLOOKUP($A10,table1,MATCH("Rent",$A$1:$E$1,0),0)

Good evening all,
I have an annoying problem which incurrs lots of additional work, whick I

would dearly like a work around for.

I have many vlookup formulae which references a named tables,eg:

A B C D E
1 M1030 200 300 400 500

I have named this list, 'table1', from which I have I have a vlookup function in another worksheet
eg =vlookup($A10,table1,3,0), where $A10 in the other worksheet is

'M1030', which will return the value '300'

This works fine, but if I insert a column in table1, eg between columns B

& C, then the vlookup formula still looks

for column 3, which is now a different value.

My question is, is it possible for the vlookup function to automatically

know to now look for the value in column 4?

Any help, as always is most appreciated.
Cheers
Mathew