Difference Between LOOKUP and VLOOKUP Functions

VLOOKUP is one of the most widely used formulas in Excel. A similar formula, from the same family for that matter, is LOOKUP, which is also commonly used to search for certain data in a table and to retrieve certain information.

These two formulas, although similar in their core functionality, are slightly different. In the example below, we will show how exactly.

Using VLOOKUP

For our example, we will use the table with students and their grades from different subjects, ranging from 50 to 100:

We will first use the VLOOKUP to find a certain result from this table. VLOOKUP stands for Vertical Lookup and it searches the value in the column located leftmost in a table. Then it returns the value from a designated column in that same row. The syntax is :

And the explanation of these values is as follows:

• lookup_value: The value that the user is searching for in the leftmost column of the table.
• table_array: The range where the data is stored.
• col_index_num: Column number (counting from the leftmost column of the table_array) from where we retrieve the data.
• range_lookup (optional): TRUE or FALSE (1 or 0) parameter. If it returns TRUE (or 1), the function performs an approximate match (sorted data required). If the FALSE (or 0) is returned, it performs an exact match.

If we want to find the grade for Jack from Geography, we can use the following VLOOKUP formula (we will put the formula in cell F2):

The retrieving result will be number 56, which is the grade that Jack got in Geography:

In our example, Jack is the lookup value (the value that we are aiming to find in the leftmost column- column A), A2:D6 is our range, where we are searching for the value, number 3 is the column index number, since we are aiming for Geography (column C), and we enter FALSE because we want to retrieve the exact match.

Using LOOKUP

For the next thing, we will use the same table to show the LOOKUP function. This function is more generic and can perform horizontal and vertical lookups. But, for it to work, data has to be sorted in ascending order.

The syntax of a regular LOOKUP looks like this:

• lookup_value: The value that we are searching for in lookup_vector.
• lookup_vector: Range that has the cell that we are looking for.
• result_vector (optional): The range of cells that contains the values you want to return. If omitted, the function will return the corresponding value from the lookup_vector.

Since the names in our table are not ordered in ascending order, we need to do it ourselves. We will select the data in the first row, then go to Data >> Filter. After that, we will choose Sort A to Z in the dropdown of the first column:

When we do this, we will have our data in the right order:

Now we can use the LOOKUP function to find the grade in Math from Monica. We will insert the following formula in cell F2:

And we will get the proper number- 97:

Difference Between LOOKUP and VLOOKUP

Looking at these two formulas, they can get us pretty similar results, but they are obviously different. These are the key differences between the two:

1. LOOKUP can deal with both vertical and horizontal data (lookups) if the data is sorted in ascending order. VLOOKUP is designed specifically for vertical lookups.
2. VLOOKUP cannot function without a column number being specified. This column number serves the purpose of retrieving the value. In our example, it was column 3, where the Geography is located. LOOKUP does not require this particular parameter to function.
3. It is easier to use VLOOKUP when the data is organized in a table with specific columns. LOOKUP is more flexible, but the downside is that it the data always needs to be sorted.

For a general conclusion, if we are dealing with a specific value in the table that is lined vertically, VLOOKUP is a clear choice. However, if we need to be more flexible and we have data sorted out, the LOOKUP function is a better choice, especially as it works with vertical and horizontal lookups.