Lookup a Value Based on a Single Criteria Across Multiple Sheets
The following formulas perform a lookup based on a single criteria across multiple sheets. In the first example, a non-array formula is used. However, the formula can become rather cumbersome if many sheets are involved in the lookup. If this is the case, the array formula in the second example can be used instead.
Using a Non-Array Formula
Sheet1
Sheet2
Sheet3
Summary
The following formula searches G2:G5 of Sheet1, Sheet2, and Sheet3, for the value in A2 of the Summary sheet, and returns the corresponding value from H2:H5...
=IF(ISNA(VLOOKUP(A2,Sheet1!G2:H5,2,0)),IF(ISNA(VLOOKUP(A2,Sheet2!G2:H5,2,0)),
VLOOKUP(A2,Sheet3!G2:H5,2,0),VLOOKUP(A2,Sheet2!G2:H5,2,0)),VLOOKUP(A2,Sheet1!G2:H5,2,0))
Based on the sample data, the formula returns 250.
Sample Workbook: Download
Using an Array Formula
Sheet1
Sheet2
Sheet3
Summary
The following
formula searches G2:G5 of each sheet listed in A2:A4 of the Summary
sheet for the value in C2 of the Summary sheet, and returns the
corresponding value from H2:H5...
=VLOOKUP(C2,INDIRECT("'"&INDEX(A2:A4,MATCH(TRUE,
COUNTIF(INDIRECT("'"&A2:A4&"'!G2:G5"),C2)>0,0))&"'!G2:H5"),2,0)
Note that the formula needs to be confirmed with CONTROL+SHIFT+ENTER. If done correctly, Excel will automatically place curly braces {...} around the formula.
Based on the sample data, the formula returns 250.
Sample Workbook: Download