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