Using a formula within a vlookup array name

[madadd]

Hello,

I have this so far:

Please Login or Register  to view this content.

The LEFT() function grabs two characters of a product number which is used to distinguish the specific tab in the costing spreadsheet. Note the two spaces on either side because each spreadsheet is named with two spaces on either side of the two character spreadsheet; " BR " for example. But I can't even get the function to work. As is, when the cursor is in the LEFT() brackets, the usual summary tip doesn't show up for it (It still shows the vlookup() summary tooltip thing). I finally got it to recognize the left function by removing the quotes:

Please Login or Register  to view this content.

But this still gives me an "Invalid External Reference to a Worksheet" error. I can easily replace the &left(F3,2)$ with a specific sheet name and everything works. Any ideas?

[NBVC]

You would need to employ the use of the INDIRECT function:

=VLOOKUP(F3,INDIRECT("'[2011-03 Costing Spreadsheet.xls] "&left(F3,2)&" '!$1:$65536"),13)

but this function only works with the source workbook open.

If you want to work with closed workbooks, you will need to download and install a free addin from here: Morefunc and use INDIREC.EXT instead of INDIRECT.. you will also need to enter the entire path of the workbook..

[madadd]

ah, ok. Why is there a need for the indirect() function?

[NBVC]

It is the only function that allows you to reference another sheet or workbook "indirectly" like you are trying to do.

http://support.microsoft.com/kb/151323

[madadd]

Hmm. So the indirect() function is returning a value, not the path. So thus I'm getting a vlookup that has a lookup array of just 1 number (which happens to be 17.01 and I don't yet know where that is coming from).

[NBVC]

The Indirect is just constructing the path based on what you hard coded and what your reference cell shows.

[madadd]

Ok. So then is there a way to use the Indirect function to return the table array address for use within vlookup?

[NBVC]

I am not sure what you mean.

Can you elaborate?

[madadd]

Well, so currently the indirect function is returning a number. But when you plug that number into the vlookup, you get
=VLOOKUP(F3,'some number here',13)
That doesn't fit so well with the array that the vlookup is asking for. What I'm looking for is variations like these:

Please Login or Register  to view this content.

So all that changes is those two letters, referring to the different sheet names, which need to be grabbed off a different cell (ie. left(F3,2)). As you see, for the vlookup formula, there needs to be an array, not just 1 number which the indirect was returning

[NBVC]

No that part: INDIRECT("'[2011-03 Costing Spreadsheet.xls] BR '!$1:$65536") should be returning the range '[2011-03 Costing Spreadsheet.xls] BR '!$1:$65536 and it should pull from the 13th column of that range.

Note that your formulas don't have the 4th (optional) FALSE argument to ensure an exact match.

[madadd]

Wow, I'm so sorry. For some reason I ALWAYS forget that the lookup value in a vlookup HAS to be in the 1st column of the array. Mine was incidentally in the third... I feel so dumb. Thank you.

I do have one more question, but I'm not sure if it needs a new thread.

In my now working, newly edited formula, I would like to add a formula for the column index number. I feel like this would somehow incorporate an hlookup(), but I don't know if or how that would work within a vlookup function, since there needs to be a number returned.

Please Login or Register  to view this content.

The array would be the same, where the top row is where I'm searching. And the lookup values are in the G column instead of the F.

[NBVC]

It would probably be something like this:

Please Login or Register  to view this content.

where G3 contains the Header value in C10:Z10 to look for and determine column index number.

[madadd]

Ah nice. One last question, I swear. My lookup value for the match() function turns out to not be exactly the same. Whether its a space added with one or the other or extra characters at the end. I tried adding in a wildcard but I can't get it to work.

...MATCH(G3"&*&",INDIRECT(....

Hopefully you get what I'm trying here.

EDIT: More specifically: ...MATCH(left(G3,2)"&*&",INDIRECT(....

[NBVC]

Maybe you mean:

Please Login or Register  to view this content.

or based on your last edit:

Please Login or Register  to view this content.

[madadd]

Sweeet. Regretfully I'm not as knowledgeable as I'd like to be in the use of ampersands :/

Thank You!