I have an unsorted range that needs to lookup a value in a number of other
ranges.
However i need the formula to know which range to lookup by looking at a
certian cell. i.e. First range is called vehicles, and the other ranges are
claeed broke, cvcom, cvcor, deals, diplo. etc.The lookup ranges are dependent
on looking at a cell that shows the sale type. e.g. I have tried the
following formula but it does not
look at the correct range for the value and returns an error.

=vlookup(match,a5,vehicles,0(g1,2) where a5 contains the lookup value and g1
contains the sale type that determines the lookup range to use.

if I substitute the g1 with an actual name of the lookup range e.g. cvcom or
deals, it returns a correct value. Is it possible for the formula to use the
correct lookup range by looking at cell g1 instead?
Thanks in advance of any help.

Regards

John

Sounds like you mean

=VLOOKUP(A5,INDIRECT(G1),2,False)

--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

"John Davies" wrote in message
...
I have an unsorted range that needs to lookup a value in a number of other
ranges.
However i need the formula to know which range to lookup by looking at a
certian cell. i.e. First range is called vehicles, and the other ranges
are
claeed broke, cvcom, cvcor, deals, diplo. etc.The lookup ranges are
dependent
on looking at a cell that shows the sale type. e.g. I have tried the
following formula but it does not
look at the correct range for the value and returns an error.

=vlookup(match,a5,vehicles,0(g1,2) where a5 contains the lookup value and
g1
contains the sale type that determines the lookup range to use.

if I substitute the g1 with an actual name of the lookup range e.g. cvcom
or
deals, it returns a correct value. Is it possible for the formula to use
the
correct lookup range by looking at cell g1 instead?
Thanks in advance of any help.

Regards

John

Possibly

=vlookup(a5,Indirect(G1),2,false)

--
Regards,
Tom Ogilvy



"John Davies" wrote:

I have an unsorted range that needs to lookup a value in a number of other
ranges.
However i need the formula to know which range to lookup by looking at a
certian cell. i.e. First range is called vehicles, and the other ranges are
claeed broke, cvcom, cvcor, deals, diplo. etc.The lookup ranges are dependent
on looking at a cell that shows the sale type. e.g. I have tried the
following formula but it does not
look at the correct range for the value and returns an error.

=vlookup(match,a5,vehicles,0(g1,2) where a5 contains the lookup value and g1
contains the sale type that determines the lookup range to use.

if I substitute the g1 with an actual name of the lookup range e.g. cvcom or
deals, it returns a correct value. Is it possible for the formula to use the
correct lookup range by looking at cell g1 instead?
Thanks in advance of any help.

Regards

John

Sorry that doesn't seem to work.
Possibly a better explanation of the problem is shown below.
VEHICLES BROKE CSMAF
CVCOM
1690220 PANDA 1.1 ACTIVE 1 11 21
1690330 PANDA 1.2 DYNAMIC 2 12 22
1690330 PANDA 1.2 DYNAMIC SKYDOME 3 13 23
1690330 PANDA 1.2 DYNAMIC AIRCON 4 14 24
1690730 PANDA 1.2 4 X 4 5 15 25
1690430 PANDA 1.2 ELEGANZA 6 16 26
1690340 PANDA 1.3 16V MULTIJET DYNAMIC 7 17 27
1690340 PANDA 1.3 16V MULTIJET DYNAMIC AIRCON 8 18 28
1690340 PANDA 1.3 16V MULTIJET DYNAMIC SUNROOF 9 19 29
1690340 PANDA 1.3 16V MULTIJET SPORTING 10 20 30
1881104 PUNTO 1.2 8V ACTIVE 3 DOOR
1881204 PUNTO 1.2 8V ACTIVE SPORT 3 DOOR

=VLOOKUP(B15,INDIRECT(C15),1,FALSE) =A6 CVCOM

Above is an example of a sheet, whereby range A2:A13 is named VEHICLES,
range B2:B13 is named BROKE, range D2:D13 is named CVCOM etc. I need to
lookup a value in the VEHICLES range and the corresponding value in 1 of the
other ranges. I have tried the above formula whereby the cell which shows
=A6 points to the
lookup vehicle (1690730 PANDA 1.2 4 X 4) and the cell which shows CVCOM
tells what range to find the value in, therefore the result should be 25.
Please advise where I am going wrong.

Thanks for any help

John


"Bob Phillips" wrote:

Sounds like you mean

=VLOOKUP(A5,INDIRECT(G1),2,False)

--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

"John Davies" wrote in message
...
I have an unsorted range that needs to lookup a value in a number of other
ranges.
However i need the formula to know which range to lookup by looking at a
certian cell. i.e. First range is called vehicles, and the other ranges
are
claeed broke, cvcom, cvcor, deals, diplo. etc.The lookup ranges are
dependent
on looking at a cell that shows the sale type. e.g. I have tried the
following formula but it does not
look at the correct range for the value and returns an error.

=vlookup(match,a5,vehicles,0(g1,2) where a5 contains the lookup value and
g1
contains the sale type that determines the lookup range to use.

if I substitute the g1 with an actual name of the lookup range e.g. cvcom
or
deals, it returns a correct value. Is it possible for the formula to use
the
correct lookup range by looking at cell g1 instead?
Thanks in advance of any help.

Regards

John

Sorry that doesn't seem to work.
Possibly a better explanation of the problem is shown below.
VEHICLES BROKE CSMAF
CVCOM
1690220 PANDA 1.1 ACTIVE 1 11 21
1690330 PANDA 1.2 DYNAMIC 2 12 22
1690330 PANDA 1.2 DYNAMIC SKYDOME 3 13 23
1690330 PANDA 1.2 DYNAMIC AIRCON 4 14 24
1690730 PANDA 1.2 4 X 4 5 15 25
1690430 PANDA 1.2 ELEGANZA 6 16 26
1690340 PANDA 1.3 16V MULTIJET DYNAMIC 7 17 27
1690340 PANDA 1.3 16V MULTIJET DYNAMIC AIRCON 8 18 28
1690340 PANDA 1.3 16V MULTIJET DYNAMIC SUNROOF 9 19 29
1690340 PANDA 1.3 16V MULTIJET SPORTING 10 20 30
1881104 PUNTO 1.2 8V ACTIVE 3 DOOR
1881204 PUNTO 1.2 8V ACTIVE SPORT 3 DOOR

=VLOOKUP(B15,INDIRECT(C15),1,FALSE) =A6 CVCOM
Above is an example of a sheet, whereby range A2:A13 is named VEHICLES,
range B2:B13 is named BROKE, range D2:D13 is named CVCOM etc. I need to
lookup a value in the VEHICLES range and the corresponding value in 1 of the
other ranges. I have tried the above formula whereby the cell which shows
=A6 points to the
lookup vehicle (1690730 PANDA 1.2 4 X 4) and the cell which shows CVCOM
tells what range to find the value in, therefore the result should be 25.
Please advise where I am going wrong.

Thanks for any help

John

"Tom Ogilvy" wrote:

Possibly

=vlookup(a5,Indirect(G1),2,false)

--
Regards,
Tom Ogilvy



"John Davies" wrote:

I have an unsorted range that needs to lookup a value in a number of other
ranges.
However i need the formula to know which range to lookup by looking at a
certian cell. i.e. First range is called vehicles, and the other ranges are
claeed broke, cvcom, cvcor, deals, diplo. etc.The lookup ranges are dependent
on looking at a cell that shows the sale type. e.g. I have tried the
following formula but it does not
look at the correct range for the value and returns an error.

=vlookup(match,a5,vehicles,0(g1,2) where a5 contains the lookup value and g1
contains the sale type that determines the lookup range to use.

if I substitute the g1 with an actual name of the lookup range e.g. cvcom or
deals, it returns a correct value. Is it possible for the formula to use the
correct lookup range by looking at cell g1 instead?
Thanks in advance of any help.

Regards

John