Vlookup across multiple files

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)

In this case, the formula is: =VLOOKUP($A$2,[Book2.xlsx]Sheet2!A1:B200,2)

vlookup multiple files

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:

=VLOOKUP($A$2,[Example_Name.xls]Sheet1!A1:C100,2)

Further reading:
How to create many to many lookup?
Backwards Vlookup