VLOOKUP across multiple sheets

E

EXCELLRNR

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

Peo Sjoblom

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/adv...er_formulas_that_can_do_this,_here_are_2_more:
 
Top