|
|
| Lookup
a Value across
Multiple Sheets |
Method One
Sheet1
Sheet2
Sheet3
Summary
The
following formula searches G2:G5 of Sheet1, Sheet2, and Sheet3, for the
value in B2 of the Summary sheet, and returns the corresponding value
from H2:H5...
=IF(ISNA(VLOOKUP(B2,Sheet1!G2:H5,2,0)),IF(ISNA(VLOOKUP(B2,Sheet2!G2:H5,2,0)),
VLOOKUP(B2,Sheet3!G2:H5,2,0),VLOOKUP(B2,Sheet2!G2:H5,2,0)),VLOOKUP(B2,Sheet1!G2:H5,2,0))
Based on the sample data, the formula returns 250. Sample
Workbook
Method Two
Sheet1
Sheet2
Sheet3
Summary
The following
formula searches G2:G5 of each sheet listed in B2:B4 of the Summary
sheet for the value in D2 of the Summary sheet, and returns the
corresponding value from H2:H5...
=VLOOKUP(D2,INDIRECT("'"&INDEX(B2:B4,MATCH(TRUE,
COUNTIF(INDIRECT("'"&B2:B4&"'!G2:G5"),D2)>0,0))&"'!G2:H5"),2,0)
| Note
that this is an array formula, which needs
to be confirmed with
CONTROL+SHIFT+ENTER for the PC or COMMAND+RETURN for the Mac. |
Based on the sample data, the formula returns 250. Sample
Workbook |
|
|
