Vlookup

TABLE 1:

TABLE 2:

TABLE 3:

TABLE 4:

FORMULA STEPS IN DETAIL

STEP 1

Assume that Sheet1 has all the data that you are looking for.
Sheet2 will contain just the Names of few of the employees who’s details you require.

STEP 2

To begin, type an = sign into Cell B2 of Sheet2.
Select the Vlookup formula from either the drop down or the fx sign circled below.

STEP 3

Click the box circled in purple.
Now select the name of the employee circled in red which is Cell A2.
After selecting Cell A2 Click the box circled in purple again.

STEP 4

Click the box circled in purple.
Then click on Sheet1.
Now select the full table as shown starting from Cell A2 to Cell D5.
Now Click the box circled in purple again.

STEP 5

Now without clicking any boxes, type 2 into the box, as shown below. ( the no. 2 stands for the second column in Sheet1, that contains the Age of the employee’s.)
Always leave the last box as 0.
(In order to easily be able to count the column no’s. when you have a lot of column’s that is, number the cloumn’s before you begin the formula.)

STEP 6

Here’s a tip - before clicking OK, type dolllar $ sign’s into the formula, as shown below.
What this does is keeps your data range fixed, whenever you drag the formula down to the other cells in the sheet.
Click OK.
( You can also get the dollar $ sign’s by placing the cursor between the formula as specified & then pressing the F4 button)

STEP 7

Before proceding further let us once again relook at the 3 basic parts of the formula.

STEP 8 12,13, 14

Now drag the formula across the entire table.
You’ll notice that column B & C have the same data.
This has happened because we have fixed the data range with the $ sign, as shown above.
Now just change the collumn no. in the formula from column no. 2 (that corresponds to Age column number in Sheet1) to column no. 4 (that corresponds to Designation column number in Sheet1), as shown in the red & purple circles.

Page updated

Google Sites

Report abuse