Figure 1. How to Lookup with Variable Sheet Name
How to Lookup with Variable Sheet Name
We can lookup data with variable sheet names in Excel with a recommendation from the outside. One of the most used function for lookup, VLOOKUP fails to lookup data with variable sheet name. However, using the VLOOKUP and INDIRECT functions together, we can lookup with variable sheet name in Excel.
Lookup with Variable Sheet Name in Excel
The VLOOKUP function can easily be linked with the INDIRECT function. It develops a Lookup_query with variable sheet name.
The generic formula to lookup with variable sheet name
Here the VLOOKUP formula looks up data on the sheets inside the INDIRECT function. It uses a dynamic reference to the names of those sheets. The lookup value is kept as a mixed reference. It makes sure the column is locked and the formula is copied across the table. This formula is useful for integrating data from several worksheets.
In this example, we will be using a company database. It shows the work hours for the employees during the first three weekdays.
Figure 2. Employee Work Hours Data Set
To integrate this in one sheet, we need to:
- Create a new sheet Worklog that will summarize the data. In cells B1 to D1, we need to assign the names of the weekdays and in cells A2 to A6 assign the name of the workers.
- Select cell B2 by clicking in it.
- Assign the formula =VLOOKUP($A2,INDIRECT(“‘”&B$1&”‘!”&”A2:B6”),2,0) to cell B2.
- Press Enter to apply the formula to B2.
- Drag the formula from using the fill handle from cells B2 to B6. Continue dragging with the selection from cells B6 to D6.
Figure 3. Example of How to Lookup with Variable Sheet Name
This will return the work hours for all the workers with variable sheet name in one sheet.
Sometimes it might be inconvenient to maintain adjustable layouts of a worksheet with others of the similar workbook. At these times the recommendations from outside will be very effective.
Most of the time, the problems you will need to solve will be more complex than a simple application of a formula or function. If you want to save hours of research and frustration, try our live Excelchat service! Our Excel Experts are available 24/7 to answer any Excel question you may have. We guarantee a connection within 30 seconds and a customized solution within 20 minutes.