CODEWITHSUNDEEP
excelvlookup
superparati
superparati

Reputation: 11

how to fix Excel VLookup return #ref! with Indirect + concatenation function to build a file path

I try to isolate each part of my excel function and can't find the solution for now. I'm using in a same row 3 functions

  1. Vlookup (search value from a specific column based on a reference/ID)
  2. Concatenate (building a file path from a tab which can be dynamic)
  3. Indirect (turning the concatenation into a text input)

I've a file located in my computer such as

content on the data_fr an an exemple

Column A Column B
row 1 1 John
row 2 2 Tintin

Those path and file names are saved in a separate excel file named test.xls and tab called "dynamic".

language Column A Column B
French desktop/folder A/ data_fr.xls
English desktop/folder A/ data_en.xls

from my new tab called "trial" of the test.xls excel file I want to collect the information from the file data_fr, tab content, data from product ID 1 (as an exemple).

if the vlookup works like a charm with the direct path, it is not working as soon as I want to create the path using indirect function

=vlookup(1,'desktop/folder A/[data_fr.xls]content'!$A:$B,2,false)
return:John

but by doing this it does not work and I don't know why.

=vlookup(A1,indirect(concatenate("'",'dynamic'!B$2,"[",'dynamic'!C$2,"]content'!$A:$B")),1,false)

return: #ref!

I've isolated the concatenation function and it works but as soon as I'm adding the indirect function it is broken. Some things is missing.

What do you have in mind which could help me? FYI: I'm using a Mac, and the files are both opens.

I try the basic vlookup and works, isolated the concatenate function to build the path and the path looks accurate. As soon as I'm adding the indirect function, it returns an error and don't know why.

Regards, Henri

Upvotes: 0

Views: 454

Answers (1)

superparati
superparati

Reputation: 11

It is now working today and to be honest I don't know very much what happened.

Two links helped me to solve my issue

I rebuilt this morning anyway my function with file Path , file name, concatenate Path & file, indirect and to finish vlookup.

VLOOKUP("datatosearch",INDIRECT(CONCATENATE("'",F4,"[",G4,"]Generic tab'!$B2:$AC14")),4,FALSE)

Job done.

Thanks

Upvotes: 1

Related Questions