varying ranges for vlookup

[bp]

How can I create a vlookup statement with the range as a variable? In other words, I need the "range_array" portion of the statement to change according to certain conditions.

[Mark W.]

...and what would those conditions be?

[bp]

I have named multiple named ranges for different years. Therefore, if I have a date given in 2001 elsewhere in the worksheet, I need the vlookup to look at the "2001 range".

[Chris Davison]

bp,

if there's a logical sequence to your multiple named ranges, this shouldn't be a problem......

I set up some called :

yr2000
yr2001
yr2002
yr2003

etc

this allowed me to VLOOKUP on a cell (say containing the word "Sammi" then in the range array I was able to interrogate the date that was sitting on "Sammi"s row

(say 13 May 2002)

as follows :

=YEAR(A1)

which brought back "2002"

since that corrosponds with the logic of my named ranges, I popped it into an indirect as part of the range array as follows :

=VLOOKUP(A1,INDIRECT("yr"&YEAR(B1)),1,0)

where A1 held "Sammi"
where B1 held "13/05/2002"
and the named range of year 2002 was "yr2002"

[bp]

[bp]

Chris,

That did exactly what I needed!

Thank you very much for your assistance!