Hi,

I have a look up on a form which is working perfectly well. I havw to enter
another column inbetween the array. I tried selected the cells that contain
the values I need by holding ctrl and giving the highlighted cells a name. It
appears that if there is a gap in the array, in this case a column, it
doesn't work.

For example

=vlookup(a1,d1:f3,2,false) is OK

but =vlookup(a1,lookup,2,false) is not working where 'lookup' are cells
d1:e3 and cells g1:g3 as I am ignorning column F.

Is there a workaround?

Thanks

Nathan

Lookup is the name of an Excel function. Give your named range a different
name, such as LOOKUPTABLE.

Dave
--
A hint to posters: Specific, detailed questions are more likely to be
answered than questions that provide no detail about your problem.


"NathanG" wrote:

Hi,

I have a look up on a form which is working perfectly well. I havw to enter
another column inbetween the array. I tried selected the cells that contain
the values I need by holding ctrl and giving the highlighted cells a name. It
appears that if there is a gap in the array, in this case a column, it
doesn't work.

For example

=vlookup(a1,d1:f3,2,false) is OK

but =vlookup(a1,lookup,2,false) is not working where 'lookup' are cells
d1:e3 and cells g1:g3 as I am ignorning column F.

Is there a workaround?

Thanks

Nathan

let the new cells be in the lookup array, the only thing whihc is neccessary
to have a dynamic vlookup
is to make sure there is a unique label above each column
for example
aa bb cc dd ee
change the vlookup to something like
=vlookup(lookup_value,Lookup_range,match(dd,lable_ range,0),0)
you can insert as many columns as you like befiore dd and the Vlookup will
give the correct answer.


"NathanG" wrote:

Hi,

I have a look up on a form which is working perfectly well. I havw to enter
another column inbetween the array. I tried selected the cells that contain
the values I need by holding ctrl and giving the highlighted cells a name. It
appears that if there is a gap in the array, in this case a column, it
doesn't work.

For example

=vlookup(a1,d1:f3,2,false) is OK

but =vlookup(a1,lookup,2,false) is not working where 'lookup' are cells
d1:e3 and cells g1:g3 as I am ignorning column F.

Is there a workaround?

Thanks

Nathan

Thanks for the BJ

I do not fully understand though. How can I enter a unique label? Are you
suggesting label range needs to be an array aswell such as

a
b
c
d

as well as the normal array?

DaveF: I wasn't using Look up as the array. It was the just the name i gave
it in the example.

Thanks

Nathan
"bj" wrote:

let the new cells be in the lookup array, the only thing whihc is neccessary
to have a dynamic vlookup
is to make sure there is a unique label above each column
for example
aa bb cc dd ee
change the vlookup to something like
=vlookup(lookup_value,Lookup_range,match(dd,lable_ range,0),0)
you can insert as many columns as you like befiore dd and the Vlookup will
give the correct answer.


"NathanG" wrote:

Hi,

I have a look up on a form which is working perfectly well. I havw to enter
another column inbetween the array. I tried selected the cells that contain
the values I need by holding ctrl and giving the highlighted cells a name. It
appears that if there is a gap in the array, in this case a column, it
doesn't work.

For example

=vlookup(a1,d1:f3,2,false) is OK

but =vlookup(a1,lookup,2,false) is not working where 'lookup' are cells
d1:e3 and cells g1:g3 as I am ignorning column F.

Is there a workaround?

Thanks

Nathan