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: 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
Hi - I’m Dave Bruns, and I run Exceljet with my wife, Lisa. Our goal is to help you work faster in Excel. We create short videos, and clear examples of formulas, functions, pivot tables, conditional formatting, and charts.
