Lookup Based on a Single Criteria Across Multiple Sheets
The formulas in the following examples 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