VLOOKUP – Mistake we do while using it in Excel

In my previous post I just gave a clear explanation on “VLOOKUP” function in excel and its attributes. Previously we just saw what different attributes are and how to enter them in vlookup function.

In this post how to copy paste the vlookup function entered in on cell to others.

We will take our previous excel only as example.

Once we enter all required attributes we go to know that our function looks as below.

=vlookup(F4, A4:C8, 2, false)

Just by dragging the cell value the function can be copied.

By dragging the value we can just copy the function, result is shown below.

But there is a drawback in dragging the value to the adjacent cells. We will look it in detail now.

When you want to fetch the value from limited cells (Limited cell for table_array) or when you want to fetch values randomly, this dragging method will not work.

Let us see this now. Observe below image properly, mainly highlighted areas @ both tables.

Names of persons were in a random manner in table 2 (Above Image).

Now observe below images.In Figure 1 we are trying to fetch data from Limited Cells. Whereas, Figure 2 we are trying to fetch data from all cells.

Click on Images to see in Full.

In both the cases the result will be as below.

This happens due to change in “table_array” values while dragging the formula form one cell to another.

When you clearly observe above image the “table_array” attribute values are getting changed.

At Cell G4 formula looks like:

=vlookup(F4, A4:C10, 2, false)

Whereas at Cell G9 formula changes to

=vlookup(F9, A9:C15, 2, false)

Why this happens?

Because we used “Relative” reference while entering values for “table_array” in cell G4. And when you drag the function value from one cell to another respective “Relative” reference will be taken.

So that’s why at G9 vlookup function will search for F9(Vinay) value in the cell range of “A9 to C15″, where it dose not exists.

This can be rectified by using absolute reference for “table_array”. If we use absolute reference value at first cell will look like

=vlookup(F4, $A$4:$C$10, 2, false)

Let us see how this works in practical.

Enter above value at cell G4.

Now drag the values from G4 to G10 and check the result.

Let us see compare values in G4 and G5 in this scenario. Check “table_array” value remained absolute and there is does not changed. Because of this we got perfect result.

Check more about vlookup here

(Will Discuss about Relative and Absolute References in another post).

Reference : Microsft Excel