The VLOOKUP Function

The VLOOKUP function searches for a value (the lookup value) in the leftmost column of a table (or database) and returns the value from a specific column in the row that contains that lookup value.

The Syntax is: =VLOOKUP(lookup_value;table_array;col_index_num;range_lookup)

The VLOOKUP Function uses four arguments.

• Lookup_Value: The value that is searched for in the leftmost column of the table (or database)
• Table_Array: The table (or database) that is being searched for
• Col_Index_Num: The column of the table (or database) from which a value (the result) is to be retrieved
• Range_LookUp: True or False
  • True (or omitted). An exact match is being searched for. If an exact match is not found, the closest value is retrieved
  • False. An exact match is being searched for. If an exact match is not found, the #N/A value is returned

I call the VLOOKUP function the function of the double U’s:

• Who – LookUp_Value
• Where – Table_Array
• What – Col_Index_Num
• Why – Range_LookUp (Optional)

Although the Internet offers a lot of documentation about the VLOOKUP function, the function is still not being used as it should be.

What are the most occurring errors?

• The LookUp_Value is not in the leftmost column of the table or database

In this case a match between the LookUp_Value and that value in the table can’t be found, while that lookup value is absolutely present in the table.

• The Table_Array is not included in the function as an absolute reference

In this case, a match between the LookUp_Value and that value in the table can’t be found, while that lookup value is absolutely present in the table. This occurs when you have several lookup values that you want to search for underneath each other.
If you do not make the reference to the table array absolute (the dollar signs!) and copy the function down, the reference to the table is copied in a relative way and therefore different for each lookup value. This is best expressed with an example.

Assume that the table with data is in C1:E5.Assume that A1:A3 contains three lookup values. When you copy the VLookUp function for the three lookup values down in a relative way, the references to the table in the VLookUp function will change.For A1 it will be C1:E5
For A2 it will be C2:E6
For A3 it will be C3:E7The reference to the table will include more empty rows each time you copy the function down (for another lookup value). You can solve this error by inserting the dollar signs in the reference to the table.

The reference to the table will then be $C$1:CE$5, which is what it should be for the three lookup values.

• The argument Col_Index_Num is invalid

This argument causes a lot of confusion.
What do you enter?
When you split the argument in three sections, you get a clue about what you should enter.

– The Col part means that you should somehow refer to a column
– The Index part means that there is some kind of logical sequence in what you enter
– The Num part tells you that you should enter a number. In other words, it is the umpteenth of a series!

If the value to look for is in column H and the table starts in column C, then the column index would be 6. Calculated from C, which is the 1st column of the table, column H is the 6th column!

Not the 8th!

• The argument Range_LookUp is incorrect

This argument causes a lot of confusion as well.
What do you enter?

TRUE seems to be the most logical choice. But is it?

• • If you work with a table where the lookup value is unique and exact, then FALSE is the right choice.
  • If you work with a table where the lookup value can be a boundary value (like a bonus table or a tax table), then TRUE is the right choice.

Illogical?
To me it was! This may have something to do with the original structure of the function.
If the intention has been that it is useful to find out how much bonus has to be paid out, or in which tax bracket someone falls, than it explains a lot.
Then you get the most answers by using TRUE as the fourth argument.

If the intention has been to find exact values in a list, then I don’t understand why the fourth argument has to be FALSE.

• The table is not sorted
  When you are looking for the nearest value in a table (with boundary values) such as that of the tax authorities, the fourth argument Range_Lookup must be TRUE. However, it is then NECESSARY to sort the values in the first column of that table in ascending order (A-Z). If the values in the first column of that table are not sorted in ascending order, you will get the wrong results!If you search for an exact value in a table, it is not necessary to sort that table. If a value exists, it will be found. If it doesn’t, you will get #N/A (Not available).

• Where is the table matrix?
  The VLookUp function is often used to search for values in a table that is not on the same worksheet as the one you are working on. Sometimes a table from another workbook is used. If you do not have control over the name of the workbook or worksheet where the table is located, another person may change that name. In that case, the VLookUp function on your worksheet will no longer find the desired results!

It will be clear that the function may also produce incorrect results when a combination of the mistakes mentioned above has been made!

Click here for a Dutch version of this tip.