How to Lookup with Variable Sheet Name

Figure 1. How to Lookup with Variable Sheet Name

Final formula: =VLOOKUP($A2,INDIRECT("'"&B$1&"'!"&"A2:B6"),2,0)

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

=VLOOKUP(Val,INDIRECT("'"&sheet&"'!"&"range"),col,0)

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.

Example

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.

Notes

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.

Our customers love us!
“The expert was absolutely amazing and stuck with me the whole way through. They were polite, patient, seemed to want to genuinely help me and provided a solution that I would never have managed otherwise. I could not be more thankful for their support and solution. Thank you!” - - Chris T, in California

Leave a Comment

avatar