Nested Vlookups

Woftae

New Member
Joined
Feb 10, 2018
Messages
8
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
 

Some videos you may like

Excel Facts

Show numbers in thousands?
Use a custom number format of #,##0,K. Each comma after the final 0 will divide the displayed number by another thousand

jasonb75

Well-known Member
Joined
Dec 30, 2008
Messages
10,982
Office Version
  1. 2019
Platform
  1. Windows
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

New Member
Joined
Feb 10, 2018
Messages
8
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

Well-known Member
Joined
Dec 30, 2008
Messages
10,982
Office Version
  1. 2019
Platform
  1. Windows
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

New Member
Joined
Feb 10, 2018
Messages
8

ADVERTISEMENT

Jasonb75 thank you for the info
 

Woftae

New Member
Joined
Feb 10, 2018
Messages
8
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

Well-known Member
Joined
Dec 30, 2008
Messages
10,982
Office Version
  1. 2019
Platform
  1. Windows
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.
 

Watch MrExcel Video

Forum statistics

Threads
1,113,875
Messages
5,544,816
Members
410,635
Latest member
phoenix7771
Top