30 Advanced Excel Tips for 2026

Excel Champs / Excel Formulas List / Learn to use INDIRECT with VLOOKUP in Excel (Formula)
By Puneet Gogia (Microsoft MVP)

Learn to use INDIRECT with VLOOKUP in Excel (Formula)

30+ Advanced Excel Tips for 2026

No spam. Only Excel Stuff. God Promise. 😊

INDIRECT turns a text string into a real cell reference, which lets one VLOOKUP pull from several sheets. Put the sheet names in a header row, then build the reference from them: =VLOOKUP($A2,INDIRECT("'"&B$1&"'!A:B"),2,0).

If B1 contains “Jan”, INDIRECT converts ‘Jan’!A:B into a live reference to that sheet, and dragging the formula across to the Feb column switches the sheet automatically. The single quotes matter — they’re what makes sheet names containing spaces work, so keep them even when your names are simple.

Two things to watch. If the sheet name in the header doesn’t exactly match a real tab, INDIRECT returns #REF! with no hint about which part is wrong, so check for trailing spaces first.

And INDIRECT can’t read a closed workbook at all — it only works on sheets in an open file. It’s also volatile, meaning it recalculates on every change in the workbook, so on a large grid of these formulas you’ll notice the slowdown.

If you want to use VLOOKUP and the data you want to look up is in different sheets, you can combine it with INDIRECT. It helps you define multiple ranges in a single formula.

indirect-with-vlookup

In the above example, we have month-wise data in three different worksheets. But with a single VLOOKUP + INDIRECT, you can get the quantity of all the products for all the months from multiple sheets.

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

To understand this formula, you need to split it into two parts:

In the first part, we have the INDIRECT function, which creates a reference to the sheet by using the name from row 1. In the example below, we refer to the range A:B of the sheet Jan.

split-formula-into-two-parts

You need to create a structure within the INDIRECT to reference the sheet with the name and the range where you have the data.

=INDIRECT("'"&B$1&"'!"&"A:B")

Once you move the formula to the Feb column, the reference in the INDIRECT changes to the sheet Feb.

reference-in-the-indirect-changes

In the second part, VLOOKUP uses the table range returned by INDIRECT and gets the value from the column you specify in col_index_num.

Important Point

In the above formula, you need the right structure to reference a range with the sheet name. If you enter the below structure in the INDIRECT:

"'"&B$1&"'!"&"A:B"

It will return:

"'Jan'!A:B"

The single quotes around the sheet name are what make the formula work with sheet names that have spaces, like “Jan 2024”. And the sheet names in row 1 must match the sheet tabs exactly, otherwise INDIRECT returns #REF!.

If a product is missing from one of the sheets, VLOOKUP returns #N/A. Wrap the formula in IFERROR to show 0 (or any other value) instead:

=IFERROR(VLOOKUP($A2,INDIRECT("'"&B$1&"'!A:B"),2,FALSE),0)

Alternative Method

INDIRECT is a volatile function. It recalculates whenever there’s any change in the workbook, which can slow down a big file. That’s why you can consider using CHOOSE. For example, with CHOOSE, you can write three formulas using VLOOKUP.

=CHOOSE(B$1,VLOOKUP($A2,Jan!$A:$B,2,0),VLOOKUP($A2,Feb!$A:$B,2,0),VLOOKUP($A2,Mar!$A:$B,2,0))
choose-with-vlookup

In this formula, as I said, we have three VLOOKUPs, and with CHOOSE, you can decide which VLOOKUP to get the result from.

with-choose-get-result-from-any-vlookup

In CHOOSE, we have referred to B1; in row 1, you have the index numbers that tell CHOOSE which formula’s value to return.

For example, when you have 2, CHOOSE will return the value of the second VLOOKUP, and for the third, it’s 3.

vlookup-value-per-choose-reference

Look Up a Value in Multiple Sheets When You Don’t Know the Sheet

Sometimes you don’t know which sheet has the value. In that case, nest VLOOKUPs inside IFERROR: if the first sheet doesn’t have the product, Excel checks the second, then the third:

=IFERROR(VLOOKUP($A2,Jan!$A:$B,2,FALSE),IFERROR(VLOOKUP($A2,Feb!$A:$B,2,FALSE),IFERROR(VLOOKUP($A2,Mar!$A:$B,2,FALSE),"Not found")))

In Microsoft 365, you can also stack the ranges of all the sheets into one with VSTACK and use a single VLOOKUP on it:

=VLOOKUP($A2,VSTACK(Jan!$A$2:$B$100,Feb!$A$2:$B$100,Mar!$A$2:$B$100),2,FALSE)

Frequently Asked Questions

Why does INDIRECT with VLOOKUP return #REF!?

The sheet name INDIRECT builds doesn’t exist: check for extra spaces or a different spelling in the header cell. INDIRECT also returns #REF! when it refers to another workbook that is closed.

Can I use INDIRECT with XLOOKUP?

Yes. Build the lookup and return ranges with INDIRECT the same way, like INDIRECT(“‘”&B$1&”‘!A:A”) for the lookup range and INDIRECT(“‘”&B$1&”‘!B:B”) for the return range.

Does INDIRECT update if I rename a sheet?

No. INDIRECT uses the text in the header cell, so when you rename a sheet, change the header cell too, or the formula returns #REF!.

Puneet Gogia

Written by

Puneet Gogia

Microsoft MVP — Excel · Founder, Excel Champs

Twelve years of teaching Excel, and a decade before that using it as a data analyst. Every tutorial here is written from a file I actually built.

3M+ readers helped
1,000+ tutorials written
Since 2015 teaching Excel

Leave a Comment