Using VLOOKUP Function With Multiple Sheets in Microsoft Excel

Microsoft Excel's VLOOKUP (a very powerful function) searches an array of cells to provide the data you choose, returning the data from the cell next to the one that it finds.

If you have created a sheet for task management then each entry may have a SSN, first name and last name, a separate column will specify each task's status. VLOOKUP searches the sheet for specific SSNs, first names and last names and returns the required information.

The following is the syntax for VLOOK:

VLOOKUP(Lookup_value, Table_array, Col_index_num, [Range_lookup]).

Lookup_value

Lookup Value is the value you want to search for. Lookup_value can be either an actual value, such as 1234, or it can be a reference to the value stored in a cell, such as A2. VLOOKUP will search for the Lookup_value in the first column of the range you specify in Table_array.

Table_array

Table Array is the range of cells that contains the data that you want to look up. Remember that the first column of your Table_array must contain the values you're trying to look up. Typically Table_array is a range of cells, like A2:B198. If you're going to copy your VLOOKUP formula to look up several values, you'll want to make your Table_array an absolute fixed reference so that when you copy the VLOOKUP formula it will always point to the same range of cells. To do that, either add dollar signs when you type the Table_array, or use the F4 key to have Excel insert the dollar signs for you.

Col_index_num

The column index number is the number of the column in the Table_array that contains the data you're looking for. VLOOKUP will try to find the Lookup_value in column 1. Col_index_num tells Excel which column in Table_array to return if it finds the Lookup_value in column 1.

Range_lookup

Range Lookup is an optional argument in VLOOKUP that can be either True or False.

Let's try with an example for a better understanding.

I have taken a sheet with the following data:



And another with data is here (Sheet 2):



Now I want to fill the data (employement status, Group, Plan Price) in Sheet 1 from Sheet2.

First click the formula bar(fx) and select a function from the selection box. (VLOOKUP). Now click on the VLOOKUP option as in the following:



Now fill in or select the values of Lookup_value, Table_Array, Col_index_num and range_lookup.



Lookup_Value

We have discussed lookeup_value is the value we want to search for. It can either be an actual address like "Value-1234" or an address such as "A2". So here we are taking the address "A2".



Now let's fill in the second value (Table_array).

Table Array is the range of cells that contains the data that you want to look up. So here in Sheet 2 we have an area from A2 to F9.



This is the Table_array. Sheet2!A2:F9.

Now to fill in col_index_num. The column index number is the number of the column in the Table_array that contains the data you're looking for.

For employement status, 4; for Group, 5; and for Plan Price, 6.



The first result for the VLOOKUP formula: =VLOOKUP(A2,Sheet2!A2:F9,4,) is q. Then we will drag it and will be the results for all rows.

Similarly VLOOKUP formula for group will be: =VLOOKUP(A2,Sheet2!A2:F9,5,FALSE)

And the formulae for the plan price will be: =VLOOKUP(A2,Sheet2!A2:F9,5,FALSE)

After filling in all the formulas we will get the final result like this:



Do more practice. It may be useful for you. Ping me if you get stuck with any problem.