Vlookup from external workbook

[mattless1]

Hi All,

Can someone help me with this formula below.
i wish to extract a cell value from a different workbook. The workbook name stays the same, but the date changes everyday

workbook Name is IBT PRINT LIST 22nd July 2019.xlsx

=VLOOKUP(B8,'[IBT PRINT LIST 22nd July 2019.xlsx]Sheet1'!B$2:X$47,5,0)

Any help would be greatly appreciated.

 

[JustynaMK]

Hi,

You can use the following formula to "produce" the adjusted Workbook Name for a given day (TODAY()):

="IBT PRINT LIST "&DAY(TODAY())&IFERROR(CHOOSE(MOD(DAY(TODAY()),10),"st","nd","rd"),"th")&" "&TEXT(TODAY(),"mmmm yyyy")

You can then reference this value (let's say it's in cell D9) in your VLOOKUP formula:

=VLOOKUP(B8,INDIRECT("'["&D9&".xlsm]Sheet1'!B$2:X$47"),5,0)

Of course you can combine these two into one formula but I thought it would be clearer to show how each component is working separately

Hope it helps.

 

[mattless1]

Thank you Justnamk,

i will give that a try today.

 

[Jon von der Heyden]

Keep in mind that the workbook will need to be open. INDIRECT will return #REF ! error if the workbook is closed. * Unless it has changed in Excel 2016 (untested).