VLOOKUP across multiple sheets
 

How can I create a lookup formula that looks at information in cell V9 and S9
in sheet 1 and finds the information in one of 5 other sheets to return the
correct value in cell K9 of sheet 1?

VLOOKUP across multiple sheets
 

But VLOOKUP is only using one lookup value? You can use this technique

=VLOOKUP(A1,INDIRECT("'"&INDEX(MySheets,MATCH(TRUE ,COUNTIF(INDIRECT("'"&MySheets&"'!A1:A50"),A1)0,0 ))&"'!A:B"),2,0)

Where a list of sheet names is named "MySheets" and it will allow a de facto
VLOOKUP over multiple sheets.
The latter formula needs to be entered with ctrl + shift & enter.



An example can be downloaded here



http://nwexcelsolutions.com/Download/3DVLOOKUP.xls






--


Regards,

Peo Sjoblom

Excel 95 - Excel 2007
Northwest Excel Solutions
www.nwexcelsolutions.com






http://www.nwexcelsolutions.com/adva..._are_2_more :
"EXCELLRNR" wrote in message
...
How can I create a lookup formula that looks at information in cell V9 and
S9
in sheet 1 and finds the information in one of 5 other sheets to return
the
correct value in cell K9 of sheet 1?