# VLOOKUP using INDIRECT reference to a tab throws #N/A

1. ## VLOOKUP using INDIRECT reference to a tab throws #N/A

Hello, All.

I have a multitab workbook, some of the tabs of which are named like pr01cc00 (no need for you to know why), and others are named like DataDict PR. As you can see, the name of the tab begins with the two characters that match the last two characters of the DataDict tab, in this case, PR.

My formula is =VLOOKUP(B2,INDIRECT("'DataDict "&UPPER(LEFT(RIGHT(CELL("filename"),8),2))&"'!\$A:\$D"),4,FALSE). Here is how to parse it.

1. Cell B2 contains a value like pr01001.
2. UPPER(LEFT(RIGHT(CELL("filename"),8),2))) pulls the first two characters off the tab name and upcases them.
3. The result is prefixed with DataDict to yield DataDict PR, the name of the DataDict tab that contains the item to be found.
4. The INDIRECT creates the reference, to which is appended an absolute reference to columns A thru D on the DataDict tab.
5. The VLOOKUP finds the target.

The formula works. But here is my problem... As I said, there are multiple tabs like pr01cc00 and I have this formula in multiple rows on each tab. The formula works, but on some tabs it throws #N/A on all rows. If I recalc the sheet, the values are found, but then other tabs throw the #N/A on all their rows. If I recalc those sheets, same story with some of the other tabs. If I recalc the workbook, same story. Not all the tabs throw the #N/A; only some do, and it changes around each time I recalc.

STOP THE MADNESS! What is going on?

2. ## Re: VLOOKUP using INDIRECT reference to a tab throws #N/A

I find it easier to just create an aggregated DATA tab, and do the vlookup on THAT instead of using a bunch of indirects. Even if said tab is literally just cell links to all the other tabs, stacked to 100k records.

3. ## Re: VLOOKUP using INDIRECT reference to a tab throws #N/A

mikeTRON,

I appreciate the work-around, but I would really like a solution to the problem itself. INDIRECT formulas useful in many respects and understanding what is causing the problem will make the use of the formula more likely in appropriate circumstances.

4. ## Re: VLOOKUP using INDIRECT reference to a tab throws #N/A

#N/A means the lookup value can't be found.

That tells me the INDIRECT must be working correctly or you'd typically get a #REF! error.

If I recalc the sheet, the values are found
What does that mean? Does that mean you have calculation set to manual?

5. ## Re: VLOOKUP using INDIRECT reference to a tab throws #N/A

Hi, Tony.

It means I go to Formulas > Calculate Now.

6. ## Re: VLOOKUP using INDIRECT reference to a tab throws #N/A

Tony,

Now that you bring it up, I do get REF errors, but they, too, go away if I recalcualte, though others appear elsewhere (but not in the same tab as the #NA). Weird. Do I need to upload the workbook?

7. ## Re: VLOOKUP using INDIRECT reference to a tab throws #N/A

It would help if you could attach the file.

Personally, I have download size limits so I might not be the one that looks at the file.

There are currently 1 users browsing this thread. (0 members and 1 guests)