Excel VLOOKUP based on Cell

[HNarli]

Hello,

I am trying to do a lookup based on a variable file name which I'd like to fetch from cell I2. I am very new to complex excel formula so am hoping someone can tell me exactly what I need to do to make this work! I've tried looking around other peoples posts on this and adapt it but get back #REF ! every time I think I am missing something so if someone could please just type the exact string for me I'd be so grateful! I'm not being lazy, I literally cannot get my head around it, so hoping if I can see it I can understand how to do this...

So, to explain.

I am trying to say look up the discount amount of a supplier based a file for each supplier I have called Supplier_Disc file which can be found: A:\Marketing\Templates (Do Not Move or Delete)\Database\Files

The file name is Supplier_Discount_XXXXX.xlsx

The file path does not change, only the supplier name 'XXXX' in blue which I have stored in cell I2.

Not sure if this is right but in cell I2 i've concatenated the words "Supplier_Discount" &B2 (which in B2 is the supplier name only).

So to clarify

Here is what I have tried so far in case it sheds some light on what I am trying to achieve, but I'm aware I've got it wrong!

=VLOOKUP($D$2,INDIRECT("'A:\Marketing\Templates (Do Not Move or Delete)\Database\Files\["&I2&".xlsx]Sheet1!$A$1:$P$500"),1,0)

Kind regards,

 

[Scott T]

Try

=VLOOKUP($D$2,INDIRECT("'A:\Marketing\Templates (Do Not Move or Delete)\Database\Files\["&I2&".xlsx]Sheet1'!$A$1:$P$500"),1,0)

When using indirect you may find it helpful make the formula without the indirect first so you can see reference you need to create in the indirect.

=VLOOKUP(D2,'A:\marketing\Templates (Do Not Move or Delete)\Database\Files\[Supplier_Discount_XXXXX.xlsx]Sheet1'!$A$1:$P$500,1,0)

Note Indirect does not like closed workbooks. Make sure all the Supplier_Discount files are open.

 

[mrmmickle1]

You can't use the Indirect() function with closed workbooks. For this solution to work all of the possible workbooks will need to be open, which is likely not what you would like to do.... You will need to use VBA to accomplish your task:

Here are some references:
https://stackoverflow.com/questions/50905740/excel-index-indirect-closed-workbook

https://groups.google.com/forum/#!m....worksheet.functions/lzqhzexSvH4/dQAPVlM3RKwJ

 

[HNarli]

Hi mrmmickle1, thanks for the quick reply. Shame to hear this as VBA scares me I was kinda hoping this one string of formula was going to solve this for me but if I can't work out the VLOOKUP I know I won't understand the VBA.
I've had a read through of your links but I cant see that they cover Vlookup within the file once it's opened. For example I sort of follow the stackoverflow link about looking in a specific cell of the open document... but I am trying to vlook up a cell based on the selected supplier name once it's open. If anyone can please help me with further explaining this or making it a bit clearer based on my scenario that would be great

Thanks

 

[mrmmickle1]

Here's a reference on how to use VBA code:

https://www.ablebits.com/office-addins-blog/2013/12/06/add-run-vba-macro-excel/

You can open a workbook and use it in a vlookup like this:

Sub AccessData()
    
    Dim DestWkBk As Workbook
    Dim SrcWkBk As Workbook
    Dim myResult As Variant
    
    Set SrcWkBk = ThisWorkbook 'This is the workbook with the value you are trying to look up
    Set DestWkBk = Workbooks.Open(Filename:="file location") 'This is the workbook of the vlookup table


    myResult = Application.VLookup(SrcWkBk.Sheets("YourWorksheet").Range("A1"), DestWkBk.Sheets("YourTableRange").Range("$A$2:$B$30"), 2, False)
    DestWkBk.Close SaveChanges:=False 'Close workbook and do not save any changes
    
End Sub