VLOOKUP, HLOOKUP, XLOOKUP in Excel or Google Sheets

At times, we know a specific value in a table or a range; and we need to get data from the corresponding cells. There are multiple ways in which we can do this.

One option is using the various LOOKUP formulas which are described on this page. The other option is using a combination of INDEX & MATCH.

VLOOKUP, HLOOKUP, XLOOKUP in Excel or Google Sheets

At times, we know a specific value in a table or a range; and we need to get data from the corresponding cells. There are multiple ways in which we can do this.

One option is using the various LOOKUP formulas which are described on this page. The other option is using a combination of INDEX & MATCH.

VLOOKUP

VLOOKUP is an extremely useful formula. It helps you find data in rows of a table based on a known value in that row.

VLOOKUP can find data in the following scenarios:

1.VLOOKUP searches data vertically; top row to bottom row
2.One data item in a row is known
3.The data that you are seeking is to the right of the data that you know
4.In case the data that you know has duplicates then the results will be returned for the first occurrence of that data

The syntax of this formula is:

EXCEL
=VLOOKUP (lookup_value, table_array, col_index_number, [range_lookup])

GOOGLE SHEETS
=VLOOKUP (search_key, range, index, [is_sorted])

lookup_value or search_key: what we are aware of; we find the other value basis this known value

table_array or range: where to look; the table or array in which we will find the unknown values

col_index_number or index: what to look for; the column number with respect to the lookup_value that we are aware of

[range_lookup] or [is_sorted]: optional value; accuracy of the information; look for the exact lookup_value (or search_key) or a close match. There are two options:

TRUE - Approximate Match
FALSE - Exact Match

Example:
Let us look at employee database as shown below and we want to know the location of specific employees.

Now let us write the formula in cell C17.

lookup_value: Provide reference of cell B17 as this is where the name of the employee for whom we are searching the location details

table_array: Give the reference of the table, B5:F14 as that is where we need to look for the data. Since the department is in column D, even if we provided the table_array as B5:E14, the formula would work fine

col_index_number: 4. The department data is in the fourth column from the Employee Name. The column number is decided from the column in which the lookup_value resides, irrespective of where the table is in the sheet

[range_lookup]: FALSE. This will look for the exact match for the employee's name

Like many other formulas, you can also use wildcard characters using the VLOOKUP formula. The formula =VLOOKUP("*Rob*", B5:F14, 4, FALSE) will return ‘Marketing’ as the answer, as it is the department value for "Fred Robinson".

VLOOKUP Formula helps getting data from a different column based on a known value

VLOOKUP with Approximate Match

VLOOKUP with an approximate match is useful especially when you are dealing with ranges.

For this formula to work, you will need to arrange your data in ascending order. In the case of an approximate match, VLOOKUP searches for the lookup_value and once it finds a value which is greater than the lookup_value it goes to one row before and returns the corresponding value in col_index_number.

Example: An organisation awards bonus to its sales staff basis the table below.

For sales less than 10,000, no bonus is awarded
For sales more or equal to 10,000 and less than 20,000 a bonus of 2% is awarded
For sales more or equal to 20,000 and less than 30,000 a bonus of 4% is awarded
For sales more or equal to 30,000 and less than 40,000 a bonus of 6% is awarded
For sales more or equal to 40,000 and less than 50,000 a bonus of 8% is awarded
For sales more or equal to 50,000 and less than 60,000 a bonus of 10% is awarded
For sales more or equal to 60,000 a bonus of 12% is awarded

For a sale of 25,000 the bonus will be 4% as then the VLOOKUP formula reaches the fourth row, i.e. cell value as 30,000, it checks that 30,000 is more than 25,000 and steps back to the third row and returns the Bonus as 4%. The formula as you can see in the image below is =VLOOKUP(B17,B6:C12,2,TRUE)

The VLOOKUP formula with Approximate Match gets the right bonus value

HLOOKUP

HLOOKUP formula helps you find data in columns of a table based on a known value in that column.

HLOOKUP can find data in the following scenarios

HLOOKUP searches data horizontally; left to right
Data item in a column is known
The data that you are seeking is in the cells below the data that you know
In case the data that you know has duplicates then the results will be returned for the first occurrence of that data.

The syntax of this formula is:

EXCEL
=HLOOKUP (lookup_value, table_array, row_index_number, [range_lookup])

GOOGLE SHEETS
=HLOOKUP (search_key, range, index, [is_sorted])

lookup_value or search_key: what we are aware of; we find the other value basis this known value

table_array or range: where to look; the table or array in which we will find the unknown values

row_index_number or index: what to look for; the row number with respect to the lookup_value that we are aware of

[range_lookup] or [is_sorted]: optional value; accuracy of the information; look for the exact lookup_value (or search_key) or a close match. There are two options:

TRUE - Approximate Match
FALSE - Exact Match

Example:
Let us look at employee database as shown below and we want to know the location of specific employees.

Now let us write the formula in cell E12:

lookup_value: Provide reference of cell D12 as this is where the name of the employee for whom we are searching the location details

table_array: Give the reference of the table, E4:N8 as that is where we need to look for the data. Since the department is in column 7, even if we provided the table_array as E4:N7, the formula would work fine

row_index_number: 4. The department data is in the fourth row from the Employee Name. The row number is decided from the row in which the lookup_value resides, irrespective of where the table is in the sheet

[range_lookup]: FALSE. This will look for the exact match for the employee name

Therefore the formula will be =HLOOKUP(D12,E4:N8,4,FALSE)

HLOOKUP formula returns the correct department name based on the row index number provided

XLOOKUP

XLOOKUP function is a significant improvement over VLOOKUP. It resolves several limitations of VLOOKUP.

Some limitations XLOOKUP addresses are:

It searches data both from top to bottom and vice versa. You can search for the first occurrence of the data or the last occurrence.
It can search for data to the left of the lookup value.
You can return a customer error message or function in case you do not find the lookup value. You had to use additional functions like IFERROR with functions like VLOOKUP.
You don't have to specify the column numbers for the data you need to return.
You can return data for multiple columns by just writing the function once. XLOOKUP leverages dynamic arrays to achieve this.
There is only one formula for searching vertically and horizontally.

The syntax of this formula is:

EXCEL
=XLOOKUP (lookup_value, lookup_array, return_array, [if_not_found], [match_mode], [search_mode])

GOOGLE SHEETS
=XLOOKUP (search_key, lookup_range, result_range, [missing_value], [match_mode], [search_mode])

lookup_value or search_key: what we are aware of; we find the other value basis this known value

lookup_array or lookup_range: where to look; the array (column/row) in which we will find the lookup_value or the search_key

return_array or result_range: what to look for; the array(s) (column/row) from which we want the return value(s)

[if_not_found] or [missing_value]: optional value; a custom message or function that will run in case you don't find lookup_value or search_key in the lookup_array or lookup_range

[match_mode]: optional value; there are four values to select from

0 - Exact match: returns the position or index of the first occurrence of value same as the lookup_value or search_key
-1 - Exact match or next smaller item: returns the position or index of the exact match. If you don't find an exact match, then it returns the position or index of the next smaller item
1 - Exact match or next larger item: returns the position or index of the exact match. If you don't find an exact match, then it returns the position or index of next larger item
2 - Wildcard character match: provide this option while using a wildcard character in the lookup_value or search_key

[search_mode]: optional value; there are four values to select from 0 - Exact match -1 - Exact match or next smaller item 1 - Exact match or next larger item 2 - Wildcard character match

1 - Search first-to-last

-1 - Search last-to-first

2 - Binary search (sort ascending order)

-2 - Binary search (sort descending order)

Example:
Let us look at an example where we have found the location of an employee based on their employee number given in cell B17.

Here is an explanation of the parameters used in the XLOOKUP formula in cell C17:

lookup_value: Cell B17, as it contains the employee number for whom we are searching the location details

lookup_array: Range C5:C14, as that is where we need to search the lookup_value

return_array: Range F5:F14, as it contains the location data

[if_not_found]: We can put a custom message, "Employee Not Found" in case the employee name is not on the list

[match_mode]: 0. This will look for the exact employee number as given in cell B17

[search_mode]: 1. Search first to last

Searching to the left of the known data

If we change the above example where we know the employee number and we need to find out the name, we can write the formula as =XLOOKUP(B17,C5:C14,B5:B14,"Employee Not Found",0,1)

Searching with wildcard characters (*, ?)

Like many other formulas in Excel, you can also use wildcard characters using the VLOOKUP formula.

The formula =XLOOKUP(B17, B5:B14, F5:F14, "Employee Not Found", 2, 1) will return ‘Chicago’ as the answer, as it is the location value for "Fred Robinson".

Wildcard character * searches for zero or more characters, therefore the * before Rob searches for "Fred " and the * after Rob searches for "inson".

Image showing XLOOKUP formula with wild card characters

Returning an array of data using XLOOKUP

XLOOKUP can return an array of data based on the known record.

In case we need to return the entire row, based on the employee name given in cell B17, we can write the formula as =XLOOKUP(B17, B5:B14, C5:F14, "Name not found", 0, 1).

We have provided the value for return_array as C5:F14. Therefore XLOOKUP returns the data in columns C to F, i.e. it returns Employee No., Salary, Department, and Location.

Searching data from the bottom of the table/range

In this example, we see that there are two records for Kane Thomas, with different salary, department, and location. This could be a scenario where Kane change his department and the second record reflects his updated record.

If you want to get the latest records (assuming new records are added at the bottom of the table) then you would perform a search of records from bottom to top to get the latest record for an employee. To get the latest record for Kane Thomas, you would write the formula as =XLOOKUP(B17, B5:B14, C5:F14, "Name not found", 0, -1).

Since we have provided the search_mode as -1, XOOKUP returns details of the record which is closer to the bottom of the table.

All Excel & Google Sheets Resources

VLOOKUP, HLOOKUP, XLOOKUP in Excel or Google Sheets

VLOOKUP, HLOOKUP, XLOOKUP in Excel or Google Sheets

VLOOKUP with Approximate Match

HLOOKUP

CONTENT

COMPANY

LEGAL

CONTENT

COMPANY

LEGAL