Nested Vlookups

Woftae

New Member
Joined
Feb 10, 2018
Messages
16
Hi my fellow excelorians
I am trying to enter a formula of nested vlookup within a concatenate statements but for some reason it ain't working and I am guessing its probably a syntax issue that I just have not been able to see (trees for the forest scenario)

The formula entered is as follows:
=VLOOKUP(E7467,CONCATENATE(VLOOKUP(D7467,Suppliers!A:M,8,FALSE),"[",VLOOKUP(D7467,Suppliers!A:M,9,FALSE),"]",VLOOKUP(D7467,Suppliers!A:M,10,FALSE),"'!A:H"),2,FALSE)

Using F9 on the concatenate in the Formula Bar to Test the Formula results in the following:
=VLOOKUP(E7467,"'C:\Users\NCU\ICloud - RABBIT HOLE PTY LTD\Price Updates\[suppliers Orders v3.xlsm]OSAL Price Check'!A:H",2,FALSE)

But the result of the formula is #VALUE
What am I not seeing? or missing?
Thanks in advance to one and all
 

Excel Facts

What do {} around a formula in the formula bar mean?
{Formula} means the formula was entered using Ctrl+Shift+Enter signifying an old-style array formula.
You need to use INDIRECT to concatenate anything which is referring to a range, i.e. INDIRECT(CONCATENATE(...))

Note that the workbook that you're getting the data from, in your example formula, [suppliers orders v3.xlsm] must be open when using this type of formula.
 
Upvote 0
You need to use INDIRECT to concatenate anything which is referring to a range, i.e. INDIRECT(CONCATENATE(...))

Note that the workbook that you're getting the data from, in your example formula, [suppliers orders v3.xlsm] must be open when using this type of formula.

Thankyou very much for replying so quickly,
If the workbook to be referenced is not open what would you suggest to use instead?
 
Upvote 0
If the workbook to be referenced is not open what would you suggest to use instead?
The full path, without concatenating
=VLOOKUP(E7467,'C:\Users\NCU\ICloud - RABBIT HOLE PTY LTD\Price Updates\[suppliers Orders v3.xlsm]OSAL Price Check'!A:H,2,FALSE)

There is no alternative that will work with a joined string and a closed workbook.

There is an add-in called MoreFunc which contains a function called INDIRECT.EXT that will open the files hidden in the background if they are not already open, but I believe that there are compatibility issues with newer versions of excel (I last used it with excel 2007).
 
Upvote 0
The full path, without concatenating
=VLOOKUP(E7467,'C:\Users\NCU\ICloud - RABBIT HOLE PTY LTD\Price Updates\[suppliers Orders v3.xlsm]OSAL Price Check'!A:H,2,FALSE)

There is no alternative that will work with a joined string and a closed workbook.

There is an add-in called MoreFunc which contains a function called INDIRECT.EXT that will open the files hidden in the background if they are not already open, but I believe that there are compatibility issues with newer versions of excel (I last used it with excel 2007).
Just incase anyone is still monitoring this thread (Jasonb75) would I be able to reference a closed workbook if I used vba (marco) to create the vlookup string and then insert into a cell as a formula, our would it still encounter the same issue if the workbook to be referenced is closed?
 
Upvote 0
would it still encounter the same issue if the workbook to be referenced is closed?
A formula created in vba works the same as a formula typed into the sheet so the same rules apply, if your formula uses INDIRECT then the source workbook must be open.
 
Upvote 0

Forum statistics

Threads
1,213,546
Messages
6,114,251
Members
448,556
Latest member
peterhess2002

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top