Problem with VLOOKUP

Hello everyone

So, i have a problem with a dynamic vlookup formula I want to make. The formula takes the first digit of a number and uses this to navigate to a specific sheet within the excel file. It then searches through a list to find the associated entry.

So, for example, if you have the number "10135) it takes the "1" from that number via "left(B2;1)".
It then makes the reference with the help of "indirect" and "concatenate", so "indirect(concatenate("Test";left(B2;1);"!A2:B21");TRUE)".
It then uses this in a vlookup formula, so "vlookup(B2;indirect(concatenate("Test";left(B2;1);"!A2:B21");TRUE);2;TRUE)".

So, the formula searches for "10135" on the sheet named "Test1" in the area A2:B21 and returns the value from the second row.

And the formula works. As long as everything is in the same file. But as soon as I try to split the formula and the data up into two separate files, the formula just throws a reference error at me.

Could someone help me with this?

Can you post a sample file: do you mean separate files (Workbooks) or separate sheets within a workbook?

To post a file:

Click "Go Advanced" and the "Paper Clip" icon. Then "Add files"==>"Browse"==> select file==>"Upload" and "Done"

So when you split file, do target sheet is there?

Originally Posted by JohnTopley

Can you post a sample file: do you mean separate files (Workbooks) or separate sheets within a workbook?

Two seperate files. See attachment for details.

Test.zip

Originally Posted by Tony Valko

The INDIRECT function won't work with references to closed files. The target file must be open.

Damn. I heard about that before. But...how do i do this then? Do you have an idea?

Damn. There has to be another way. With formulas.

So, I'm telling you guys the story behind this: we have a big file with multiple worksheets for items we sell. 6 worksheets to be exact, each one is a special category and filled with about 2'000 items and their item numbers.
The item numbers on the first sheet start with a "1". Second sheet is "2", you get the concept.
And now we have some documents, like a delivery note for example, where you put in the item number and it shows the information to that item through a vlookup-formula.
And because the items are on 6 sheets I need to write the vlookup-part 6 times in one formula.
And if you want to copy the formula to another cell, it takes up to 30 seconds. For one cell! And if you open up a document with a lot of these vlookup-formulas, it takes aaages to fetch all the data.

That's the whole story behind my problem. I'm just trying to speed up the whole formula, and the documents where the formula is used in.

It would help if you post a small sample file (no more than 3 worksheets) plus any document sheets, showing required results.

Isn't that enough?

Originally Posted by JanB1

Two seperate files. See attachment for details.

Attachment 442715

Okay, I'm sorry. Imma reupload the files. The sheet called "Main" is the important one. I added some comments.

Test.zip

What I need is something like the second formula with concat. The formal I wrote doesn't work when the "Test1.xlsx" is closed, because I'm using "indirect" I guess. But without the "indirect" the "vlookup" throws out an error. I want to shorten the old formula that works simply by going through all possibilities with 6 nested "IF"s. So, "if 1 goto A, else if 2 goto B, else if 3 goto C (etc)"to put it into simple pseudo-code. The new formula should just take the first number of the item number and directly go to the corresponding sheet in the data source.

As was stated earlier INDIRECT does not work with closed files. See post #7.

So you either have the file(s) open or you look at unsupported function mentioned by Tony.

the first VLOOKUP in my last post will look at the appropriate sheet in a single workbook.

The second will do the same for OPEN workbooks.

Originally Posted by JohnTopley

As was stated earlier INDIRECT does not work with closed files. See post #7.

Originally Posted by JanB1

The formal I wrote doesn't work when the "Test1.xlsx" is closed, because I'm using "indirect" I guess. But without the "indirect" the "vlookup" throws out an error.

Yeah, I got that.

Problem is, that the formula DOESN'T work without it (the "indirect", because it can't interpret the string. Is there any other function that takes a string and returns it as a reference?).
And that add-on...welp I'm not the only person using this file. And because it's no longer supported...I don't know.

Well ****. Okay.

Yeah. Can't make a single file outta it. :/