Nested Vlookups

[Woftae]

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

 

[jasonb75]

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.

 

[Woftae]

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?

 

[jasonb75]

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).

 

[Woftae]

Jasonb75 thank you for the info

 

[Woftae]

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?

 

[jasonb75]

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.