You can use the VLOOKUP and INDIRECT functions together in Excel to look up a value in a table in a different worksheet or workbook.
The VLOOKUP function searches for a value in the leftmost column of a table and returns a value in the same row from a specified column. The INDIRECT function returns a reference to a cell, based on a text string.
Here’s an example of how you can use these functions together:
Assume you have three worksheets in your workbook: 2021, 2022, and Summary.
In the 2021 and 2022 worksheets, you have datasets that contain regional sales figures for 2021 and 2022, with Region and Total Sales (USD) columns.
In the Summary worksheet, you want to create a summary report that lists the 2021 and 2022 sales by region.
Here are the steps to accomplish this:
- Select cell B2 in the Summary worksheet and enter the formula below:
- Drag the fill handle to copy the formula down column B and across to column C:
Notice that the formula has returned the 2021 sales figures from the 2021 worksheet in column B of the summary report and the 2022 sales from the 2022 worksheet in column C.
Explanation of the formula
The syntax of the VLOOKUP function is as follows:
=VLOOKUP(lookup_value, table_array, col_index_num, [lookup_range])
- The lookup_value is in cell A2.
- The table_array is formulated by INDIRECT(“‘”&B$1&”‘!”&”A2:B8”). The mixed reference B$1 refers to the column heading 2021, which matches the name of the 2021 worksheet. To create a particular worksheet reference, the exclamation point (!) is joined to the right of the mixed reference. To reference a cell range on the worksheet, A2:B8 is joined to the right of the exclamation point. The result of the concatenation is the text “2021!A2:B8,” which is used by the INDIRECT function to return a table array to be used by the VLOOKUP function.
- The col_index_num is 2 because we want to return the sales figures in column 2 of the table array.
- The lookup_range is FALSE because we want an exact match.
This tutorial showed an example of how to use the VLOOKUP and INDIRECT functions together in Excel. We hope you found the tutorial helpful.