Have you ever wondered if it is possible to do a vlookup across multiple files? Yes, it is possible. Here you can find how to do that in Excel.
Vlookup formula with two spreadsheets
You want to just do a vlookup. But the difficulty is that you have to store the data in two different Excel files. The files are in the same directory and named Book1.xlsx and Book2.xlsx.
The simple vlookup formula would be: =VLOOKUP($A$2,A1:B200,2)
Once you have a consolidated data source, you can use the VLOOKUP function to retrieve values from the data. In the VLOOKUP function, you will use the name of the consolidated data source as the table array argument and the values from the other Excel files as the lookup values.
In this case, the formula is: =VLOOKUP($A$2,[Book2.xlsx]Sheet2!A1:B200,2)
How it works?
To refer your vlookup formula to another Excel file, just write [File_Name.extension]Sheet_Name!
If you have your data in Example_Name.xls in Sheet1 and A1:C100 cells the formula would be:
In general, consolidating data from multiple Excel files can become complicated and time-consuming, especially if the data sets are large or if they change frequently. In these cases, it may be more efficient to use a database management system to store and analyze the data.
Further reading: How to create many to many lookup? Backwards Vlookup