Exceljet

Quick, clean, and to the point

Lookup with variable sheet name

Excel formula: Lookup with variable sheet name
Generic formula 
=VLOOKUP(val,INDIRECT("'"&sheet&"'!"&"range"),col,0)
Explanation 

To create a lookup with a variable sheet name, you can use the VLOOKUP function together with the INDIRECT function.

In the example shown, the formula in C5 is:

=VLOOKUP($B5,INDIRECT("'"&C$4&"'!"&"B5:C11"),2,0)

How this formula works

The "month" tabs of the worksheet contain a table that looks like this:

Example of month data sheet for January

The VLOOKUP formulas on the summary tab lookup and extract data from the month tabs, by creating a dynamic reference to the sheet name for each month.

The lookup value is entered as the mixed reference $B5, with the column locked to allow copying across the table.

The table_array is created using the INDIRECT function like this:

INDIRECT("'"&C$4&"'!B5:C11")

The mixed reference C$4 refers to the column headings in row 4, which match sheet names in the workbook (i.e. "Jan", "Feb", "Mar").

A single quote character is joined to either side of C$4 using the concatenation operator (&). This is not required in this particular example, but it allows the formula to handle sheet names with spaces.

Next, the exclamation point (!) is joined on the right to create a proper sheet reference, which is followed by the actual range for the table array.

Finally, inside VLOOKUP, 2 is provided for column index with 0 to force an exact match.

Author 
Dave Bruns

Excel Formula Training

Formulas are the key to getting things done in Excel. In this accelerated training, you'll learn how to use formulas to manipulate text, work with dates and times, lookup values with VLOOKUP and INDEX & MATCH, count and sum with criteria, dynamically rank values, and create dynamic ranges. You'll also learn how to troubleshoot, trace errors, and fix problems. Instant access. See details here.