how to use 2 worksheets in one formula with INDIRECT & VLOOKUP

[NEWB]

I am trying to pull in information from two different worksheets into one
column, but some of the inofrmation from one sheet is different from the
other, and some of the information is the same, and if it chages I want it to
change automatically so right now I have this formula which does NOT work
---- =VLOOKUP(A5, INDIRECT("'"& $C$1 &" information'!$A$4:$L$97"; "'"& D1 &"
information'!$A$4:$L$97"),12,FALSE)) ----- the C$1$ and D$1$ are references
for 2005 and 2004 which would pull from those sheets, so if i change it to
2006 it will pull from the 2006 sheet automatically. Am i way off on the
formula? Or is this even possible? If it is HOW?

 

[Biff]

Hi!

Am i way off on the formula?

Yes and no.

You can't use multiple lookup tables like that.

While it would be technically possible to return multiple values from 2
different lookups to a single cell, it is (would be) a real pain to
maintain. Why don't you just use separate cells? It would be MUCH easier!

Otherwise, use this as a guide:

=VLOOKUP(A5,INDIRECT("'"&C1&"'!A1:B5"),2,0)&","&VLOOKUP(A5,INDIRECT("'"&D1&"'!A1:B5"),2,0)

Biff