Vlookup using a cell value as search criteria
 

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

Vlookup using a cell value as search criteria
 

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

Vlookup using a cell value as search criteria
 

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

Vlookup using a cell value as search criteria
 

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

Vlookup using a cell value as search criteria
 

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