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:

1 |
=VLOOKUP($A2,INDIRECT("'"&B$1&"'!"&"A2:B8"),2,FALSE) |

- 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

1 |
=VLOOKUP($A2,INDIRECT("'"&B$1&"'!"&"A2:B8"),2,FALSE) |

The syntax of the VLOOKUP function is as follows:

=VLOOKUP(lookup_value, table_array, col_index_num, [lookup_range])

Therefore:

- 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.

## Conclusion

This tutorial showed an example of how to use the VLOOKUP and INDIRECT functions together in Excel. We hope you found the tutorial helpful.