Explanation
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:
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:
=VLOOKUP($B5,INDIRECT("'"&C$4&"'!"&"B5:C11"),2,0)
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:
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 (&). 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:
=VLOOKUP($B5,INDIRECT("'Jan'!B5:C11"),2,0)
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.
