Lookup a Value 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
Looking Up a Value Based on a Single Criteria Across Multiple Sheets - Sheet1 - Method One Looking Up a Value Based on a Single Criteria Across Multiple Sheets - Sheet2 - Method One Looking Up a Value Based on a Single Criteria Across Multiple Sheets - Sheet3 - Method One Looking Up a Value Based on a Single Criteria Across Multiple Sheets - Summary Sheet - Method One

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
Look Up a Value Based on a Single Criteria Across Multiple Sheets - Sheet1 - Method Two Look Up a Value Based on a Single Criteria Across Multiple Sheets - Sheet2 - Method Two Look Up a Value Based on a Single Criteria Across Multiple Sheets - Sheet3 - Method Two Look Up a Value Based on a Single Criteria Across Multiple Sheets - Summary Sheet - Method Two

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