Quick, clean, and to the point

Lookup with variable sheet name

Excel formula: Lookup with variable sheet name
Generic formula 

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:


In this example the goal is to create a lookup formula with a variable sheet name. In other words, a formula that uses the sheet name in a way that can be changed by referring to up a value on the worksheet. The key to the solution is the INDIRECT function, which tries to evaluate text as a worksheet reference. This makes it possible to build a formula to assemble a reference as text using concatenation, and use the resulting text as a valid reference.

The workbook contains four worksheets: one summary, and three months,  "Jan", "Feb", and Mar". Each of the month sheets has the same structure, which looks like this:

Example of month data sheet for January

The 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, where the names for each sheet are the month names in row 4.

The VLOOKUP function is used to perform the lookup. The formula in cell C5 is:


Inside VLOOKUP, 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:


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 (&). The single quotes are not required in this particular example, but they allow the formula to handle sheet names that contain spaces in other situations. 

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. The result of this concatenation is text, which INDIRECT then evaluates as a proper reference:


Finally, inside VLOOKUP, 2 is provided for column index with zero (0) as the final argument to force an exact match. As the formula is copied down and across, VLOOKUP retrieves the correct values from each sheet.

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.

Download 100+ Important Excel Functions

Get over 100 Excel Functions you should know in one handy PDF.