Comparison of the Excel Lookup, Vlookup and Hlookup Functions

Comparison of the Excel Lookup, Vlookup and Hlookup Functions

The Excel lookup functions - Lookup, Vlookup and Hlookup - are three of the most useful functions supplied by Excel. However, many users get these functions (particularly the Lookup and Vlookup functions) mixed up, and as a result, find that their function doesn't work as expected.

This article aims to clarify the difference between the Excel Lookup, Vlookup and Hlookup functions, and to provide an overview of each of these functions.

All three functions - the Lookup, Vlookup and Hlookup - look up a value in a list and return the corresponding value in a second list. However, each of the functions is designed to work slightly differently.

One of the main considerations when selecting a lookup function is whether your data is organised along rows (i.e. horizontally) or down columns (i.e. vertically). If organised horizontally, you can use either the Hlookup function or Lookup function, and if your data is organised vertically, you can use either the Vlookup function or the Lookup function.

It is also important to consider what you need to happen if the function fails to find an exact match to your lookup value. If you only want to return a value if an exact match is found, and if not, you want an error message to be returned, you will need to use the Vlookup or the Hlookup function (with the range_lookup argument set to FALSE). However, if you want to accept the closest match to your lookup value, any of the three lookup functions can be used to do this.

The three functions (including the two different forms of the Lookup function) are summarised below:

Lookup Function

Vector Form: The vector form of the Excel Lookup function uses two vectors (i.e. Two one-dimensional arrays). As these two vectors are supplied to the function as separate arguments they can be horizontal or vertical, and there are no limitations on their positions in relation to each other.

Array Form: The array form of the Excel Lookup function uses a 2-dimensional array. The first column or row of the array is searched for the lookup value and the value returned is taken from the last column or row. Therefore, this function relies on the data being organised in a table, and the column or row containing the values to be returned must after (but not necessarily adjacent to) the column or row being searched.

Both forms of the Lookup function search for a closest match. I.e. If an exact match to the lookup value is not found, the closest match below the lookup value is used instead.

Vlookup Function

The Excel Vlookup function uses a 2-dimensional array with the data organised in columns. The first column of the array is searched for the lookup value and the user specifies which column the value returned is to be taken from. Therefore, similar to the array form of the Lookup function, the column containing the values to be returned must be to the right of the column to be searched.

The main difference between the Vlookup function and the array form of the Lookup function is that the Vlookup function can be set (via the range_lookup argument) to only return a value if it finds an exact match. However, by default (i.e. If the range_lookup argument is not supplied) the Vlookup function will, like the Lookup function, return the closest match below the lookup value.

Hlookup Function

The Excel Hlookup is the horizontal version of the Vlookup function. The function uses a 2-dimensional array, with the data arranged in rows. The first row of the array is searched for the lookup value and the user specifies which row the value returned is to be taken from. Therefore, in the Hlookup function, the row containing the values to be returned must be below (but not necessarily adjacent to) the searched row.

Also, like the Vlookup function, the Hlookup function uses the range_lookup argument, which allows the user to specify how the function behaves if it fails to find an exact match. If this argument is set to FALSE, the Hlookup function only returns a value if it finds an exact match to the lookup value. However, by default (i.e. If the range_lookup argument is not supplied or is set to TRUE), the Hlookup function will return use the closest match below the lookup value.

It is important to note that in all of the above functions, if you are not requesting an exact match to your lookup value, the data in the column or row to be searched must be sorted into ascending order. If it isn't, this could cause your function to return unexpected results.