View original (French)

VLOOKUP issue on another workbook with a variable path

Lauhick Posted messages 4 Status Member -  
 Paliora -
Hello everyone!

To explain my problem, I would like to search for a value in a different folder than the one containing my workbook and have the ability to mark on an assigned cell the name of the file I want to search in.

I have tried several possibilities such as using VLOOKUP with this formulation:

=VLOOKUP(B21; " 'C:\Users\NG7ABDE\Desktop\Dossier\[" & B26 & ".xlsx]Sheet1'!$1:$1048576
"; 2)

The problem is that it cannot create the path...
I also tried with the CONCATENATE function.

I have already managed to create this function with a macro, but my goal now is to do without it.

I hope you can help me! :)

Thank you in advance!

Configuration: Windows / Chrome 68.0.3440.75

1 answer

  1.
    Vaucluse Posted messages 27336 Registration date   Status Contributor Last intervention   6 453
     
    Hello
    try with:
    =VLOOKUP(B21, INDIRECT("'C:\Users\NG7ABDE\Desktop\Dossier\[" & B26 & ".xlsx]Sheet1'!$A1:$B1048576"), 2)
    which should adjust to the workbook name in B26
    crdlmnt

    --
    The quality of the response mainly depends on the clarity of the question, thank you!
    0
    1. Lauhick Posted messages 4 Status Member
       
      Unfortunately, it doesn't seem to be working; I just tried it, and it shows me the error #REF!
      0
    2. Vaucluse Posted messages 27336 Registration date   Status Contributor Last intervention   6 453
       
      I may have made a mistake in the model
      Check that what you write between the parentheses of INDIRECT corresponds exactly to the address you want to obtain (in text, with texts, signs, etc..)
      for that write the entire part between these parentheses (without the INDIRECT code, but with all the quotes) in a cell outside the range, starting with the equal sign, check the resulting label, correct if necessary to get the right code
      and then insert it into the formula
      crdlmnt
      0
    3. Lauhick Posted messages 4 Status Member
       
      Yes, yes, I had already checked it like that and the result perfectly matches the expected result. To tell you, I even copied this result as a value and then copied it directly into my search matrix, and the function gave me the expected result.

      It seems that Excel struggles to understand that this string of characters is a folder path when using a function.
      0
    4. Lauhick Posted messages 4 Status Member
       
      Oh, I just noticed something; your solution works if the workbooks we want to search in are open. My goal is not to have to open them.
      0
    5. Vaucluse Posted messages 27336 Registration date   Status Contributor Last intervention   6 453
       
      So I think we need to go through VBA. This is not my area
      good luck and safe travels
      best regards
      0