Using Vlookup for multiple sheets in Excel 2007

Vlookup is one function that is widely used across excel users. However, for those who are beginning to use Excel, this post will give a few tips on how to use Vlookup across multiple sheets. So, before we begin, here is a brief intro about what vlookup does.

The function is used to retrieve values corresponding to a particular value. This value might be in a particular sheet or in some other sheet. It is the latter that shall be looked into in this post.

Let’s say there are a series of values in a sheet- Temp1.xls in the following manner.

vlookup-across-multiple-sheets_i
Using Vlookup for multiple sheets in Excel 2007 1

Now say there is a series of values in another sheet named, Sheet 2

vlookup-across-multiple-sheets_ii
Using Vlookup for multiple sheets in Excel 2007 2

Tiger is an entry that is there in the file temp2.xls and also present in file temp1.xls; say you want to find the value corresponding to the tiger in file temp1. Before you type the formula in the formula bar of file temp2.xls, make sure you have saved the file from which you want values to be searched!

Having saved the file, now for the formula:

(i) For the first parameter- Type the formula =vlookup(“tiger”,

vlookup-across-multiple-sheets_i
Using Vlookup for multiple sheets in Excel 2007 3

(ii) For the second parameter- press ctrl+pagedown and switch to the file where the entry is present, select the table i.e. which would get selected in the following manner:

vlookup-across-multiple-sheets_iii
Using Vlookup for multiple sheets in Excel 2007 4

(iii) Now complete the formula =VLOOKUP (“Tiger”,Sheet1!G3:H10,2,0). This would throw the value corresponding to Tiger in the second row, which is Tim in the desired cell.

In case of multiple files, don’t forget to save the files

Press the AL+ tab to switch to the file where the source table is located,

Select the table and enter the column number whose value corresponding to the value entered in the formula is needed

In plain words, follow the above-mentioned steps, and you will get your desired result!

For more tips on excel watch out for this space.

Scroll to Top