Vlookup using a cell value as search criteria

[John Davies]

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

[Bob Phillips]

Sounds like you mean

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

--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

"John Davies" <[email protected]> wrote in message
news:[email protected]...
> 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
>
>
>

[Tom Ogilvy]

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
>
>
>

[John Davies]

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" <[email protected]> wrote in message
> news:[email protected]...
> > 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
> >
> >
> >
>
>
>

[John Davies]

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
> >
> >
> >