Auto updation of vlookup

[Melvinantonyraj]

Hey!

I have a major doubt regarding Vlookup.

I am currently using Vlookup to fetch data across workbooks with help of the workbook location as the reference for table array, as mentioned below

VLOOKUP($B12,'Z:\C Drive New\VLOOK UP-S9\S9 PART\[PART_MAY-2022-11.xls]Report'!$A$14:$B$94,2,)

whereas I need auto filling of the serial numbers of the workbooks i.e. the underlined section,

11 - PART_MAY-2022-11.xls

12 - PART_MAY-2022-12.xls

13- PART_MAY-2022-13.xls and so on

Is there any specific function or code to automate the auto filling of the serial number of the workbooks? As it consumes a lot of time when I need to compile thousands of workbooks by manually editing the serial numbers.

Please help me out team. Thanks in advance!!!

[#2]

@Melvinantonyraj, Not sure how the dataset looks but try adding 1 new column for the new formula and 1 for the file number (if it doesn't already exist). and  use a combination of indirect substitute and formulatext functions. PFA an example