VLOOKUP, HLOOKUP, and XLOOKUP in Excel

VLOOKUP, HLOOKUP, and XLOOKUP in Excel

When working with data in Microsoft Excel, we need to find a specific value from a table and return related information. It provides several lookup functions that make the task easier. The three main lookup functions are VLOOKUP, HLOOKUP, and XLOOKUP. These functions are useful for finding data from rows or columns without manually searching through a large worksheet.

In this article, we will learn about VLOOKUP, HLOOKUP, and XLOOKUP in Excel, including their syntax, how they work, examples, and the differences between them.

What Are Lookup Functions in Excel?

Lookup functions are used to search for a specific value in a range or table and return a related value.

For example, suppose we have a table containing employee IDs, names, departments, and salaries. Instead of manually searching for an employee ID, we can use a lookup function to find the ID and return the employee's name or salary.

The commonly used lookup functions are

• VLOOKUP: It is used to search vertically through the first column of a table.
• HLOOKUP: It is used to search horizontally through the first row of a table.
• XLOOKUP: It is a modern and more flexible lookup function that can search both vertically and horizontally.

VLOOKUP in Excel

VLOOKUP stands for Vertical Lookup. It searches for a value in the first column of a table and returns a related value from another column in the same row. It is used when data is arranged vertically in columns.

VLOOKUP Syntax

It has the following syntax.

=VLOOKUP(lookup_value, table_array, col_index_num, [range_lookup])

VLOOKUP Arguments

• lookup_value: The value that you want to search for in the table. 
• table_array: The range of cells that contains the data you want to search.
• col_index_num: The column number from which Excel should return the result.
• range_lookup: It specifies whether you want an exact match or an approximate match.

Example of VLOOKUP

Suppose we have the following data:

To find the name of the employee whose ID is 103, we can use:

=VLOOKUP(103,A2:C5,2,FALSE)

The formula searches for 103 in the first column and returns the corresponding value from the second column.

Output:

Priya

Important Points About VLOOKUP

• VLOOKUP searches from top to bottom.
• The lookup value must be in the first column of the selected table.
• It normally returns a value from a column to the right of the lookup column.
• FALSE is used for an exact match.
• TRUE can be used for an approximate match.
• The column number is counted from the selected table range, not necessarily from the worksheet's column letters.

HLOOKUP in Excel

HLOOKUP stands for Horizontal Lookup. It searches for a value in the first row of a table and returns a related value from another row in the same column. It is useful when data is arranged horizontally in rows.

HLOOKUP Syntax

=HLOOKUP(lookup_value, table_array, row_index_num, [range_lookup])

HLOOKUP Arguments

• lookup_value: The value that you want to search for in the table.
• table_array: The range of cells that contains the data.
• row_index_num: The row number from which Excel should return the result.
• range_lookup: It Specifies whether you want an exact match or an approximate match

Example of HLOOKUP

Suppose the data is arranged horizontally:

To find the name associated with ID 103, we can use:

=HLOOKUP(103,A1:E3,2,FALSE)

The formula searches for 103 in the first row and returns the corresponding value from the second row.

Output:

Priya

Important Points About HLOOKUP

• HLOOKUP searches horizontally.
• It searches for the lookup value in the first row.
• It returns a value from a specified row.
• FALSE can be used for an exact match.
• TRUE can be used for an approximate match.
• The row number is counted from the selected table range.

XLOOKUP in Excel

XLOOKUP is a modern lookup function that can search for a value in a range and return a corresponding value from another range. It does not require a column or row index number to return the result. It can perform both vertical and horizontal lookups, making it more flexible for many lookup tasks.

XLOOKUP Syntax

The following syntax is given below:

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

XLOOKUP Arguments

• lookup_value: It is the value that you want to search for in a table. 
• lookup_array: It is the range where the function searches for the lookup value.
• return_array: It represents the range from which the matching value is returned.
• if_not_found: It is an optional value or message displayed when no match is found.
• match_mode: It specifies how the lookup value should be matched.
• search_mode: It specifies the order in which the function searches for the lookup value.

Example of XLOOKUP

Using the same employee data:

To find the name associated with ID 103, we can use:

=XLOOKUP(103,A2:A5,B2:B5)

The formula searches for 103 in the ID range and returns the corresponding value from the Name range.

Output:

Priya

XLOOKUP with a Custom Not Found Message

XLOOKUP allows us to specify what should be displayed when a matching value is not found.

For example:

=XLOOKUP(105,A2:A5,B2:B5,"Employee not found")

If ID 105 does not exist, Excel returns:

Employee not found

This makes the result easier to understand than displaying an error such as #N/A.

Difference Between VLOOKUP, HLOOKUP, and XLOOKUP

Although all three functions are used for finding data, they work differently.

Advantages of Using Lookup Functions in Excel

Lookup functions in Excel provide several benefits when working with data. Some of the main advantages are as follows:

• They help find information quickly. 
• They reduce manual searching in large datasets. 
• They help connect related data from different ranges. 
• They can be used to create automated reports. 
• They make it easier to analyze large datasets. 
• They help in creating dynamic worksheets. 
• They reduce repetitive work. 
• They are used to retrieve related values from tables.

Frequently Asked Questions

What is VLOOKUP?

VLOOKUP is an Excel function that searches for a value vertically in the first column of a table and returns a related value from another column.

What is HLOOKUP?

HLOOKUP searches horizontally for a value in the first row of a table and returns a related value from another row.

What is XLOOKUP?

XLOOKUP is a flexible Excel lookup function that searches a lookup range and returns a corresponding value from another range.

Which Excel function can perform both vertical and horizontal lookups?

XLOOKUP can be used for both vertical and horizontal lookup operations.

Does VLOOKUP require a column index number?

Yes. VLOOKUP requires a col_index_num argument to specify which column should provide the result.

Does HLOOKUP require a row index number?

Yes. HLOOKUP requires a row_index_num argument to specify which row should provide the result.

Can XLOOKUP display a custom message when a value is not found?

Yes. XLOOKUP provides the optional if_not_found argument for displaying a custom result.

Conclusion

VLOOKUP, HLOOKUP, and XLOOKUP are useful Excel functions for finding and retrieving related information from datasets. We can use these lookup functions to work more efficiently with Excel data analysis, reports, spreadsheets, and large datasets.