How to Use VLOOKUP, HLOOKUP, and XLOOKUP in Excel: A Comprehensive Guide

Excel is one of the most powerful tools for data analysis, and functions like VLOOKUP, HLOOKUP, and XLOOKUP make it even more efficient when working with large datasets. These functions are designed to help you find specific data from a table based on a given lookup value. In this blog article, we’ll walk through each of these lookup functions, their uses, and provide examples with real-world data to help you understand their applications.

📈 VLOOKUP: Vertical Lookup

VLOOKUP (Vertical Lookup) is a function in Excel used to search for a value in the leftmost column of a table and return a value in the same row from a specified column. It’s called a “vertical” lookup because the search happens in a vertical column structure.

✍️ Syntax of VLOOKUP:

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

lookup_value: The value you want to search for.

• table_array: The table or range of data where the lookup will be performed.
• col_index_num: The column number (starting from 1) that contains the value you want to retrieve.
• [range_lookup]: Optional. TRUE for an approximate match, or FALSE for an exact match.

✅ Example Use:

Let’s say you have a list of products with their IDs and prices in a table. You want to find the price of a product based on its ID.

Data Table: Product ID and Price

You want to know the price of the product with ID 102.

Formula:

=VLOOKUP(102, A2:C5, 3, FALSE)

Result: $1.00

📊 HLOOKUP: Horizontal Lookup

HLOOKUP (Horizontal Lookup) works similarly to VLOOKUP, but it searches for the lookup value in the topmost row of a table and returns the value from a specified row in the same column.

✍️ Syntax of HLOOKUP:

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

• lookup_value: The value you want to search for.
• table_array: The table or range of data where the lookup will be performed.
• row_index_num: The row number (starting from 1) that contains the value you want to retrieve.
• [range_lookup]: Optional. TRUE for an approximate match, or FALSE for an exact match.

✅ Example Use:

Let’s say you have a sales data table, and you want to find the sales amount for a specific product in a given month.

Data Table: Monthly Sales for Products

You want to find the sales for Product B in March.

Formula:

=HLOOKUP("Product B", A2:D4, 3, FALSE)

Result: $1,000

🔄 XLOOKUP: The Future of Lookups

XLOOKUP is the newest and most versatile lookup function in Excel, introduced in Excel 365 and Excel 2021. It allows you to search a range or array and return a value from another range or array, either vertically or horizontally. XLOOKUP is considered an upgrade over VLOOKUP and HLOOKUP due to its enhanced functionality.

✍️ Syntax of XLOOKUP:

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

• lookup_value: The value to search for.
• lookup_array: The array or range to search for the value.
• return_array: The array or range containing the result.
• [if_not_found]: Optional. The value to return if the lookup value is not found.
• [match_mode]: Optional. The type of match (0 for exact match, 1 for approximate match).
• [search_mode]: Optional. The direction to search (1 for searching from the beginning, -1 for searching from the end).

✅ Example Use:

Let’s look at a similar table to the one used for VLOOKUP and HLOOKUP, but now we’ll use XLOOKUP to find the product price.

Data Table: Product ID and Price (Same as the VLOOKUP Example)

You want to find the price of the product with ID 103.

Formula:

=XLOOKUP(103, A2:A5, C2:C5, "Not Found")

Result: $1.20

🔄 Comparing VLOOKUP, HLOOKUP, and XLOOKUP

• Direction of Search:
  • VLOOKUP: Searches vertically (columns).
  • HLOOKUP: Searches horizontally (rows).
  • XLOOKUP: Searches both vertically and horizontally, providing greater flexibility.

• Flexibility:
  • VLOOKUP: Limited to searching from the first column.
  • HLOOKUP: Limited to searching from the first row.
  • XLOOKUP: No such limitations—can search anywhere in the array and return values from anywhere.

• Handling Errors:
  • VLOOKUP and HLOOKUP: If a value is not found, they return an error (#N/A).
  • XLOOKUP: Allows you to specify a custom message when the value is not found.

🎓 Real-World Use Cases

1. Employee Directory
   • Task: Get salary based on ID
   • Function: VLOOKUP
2. Quarterly Financial Report
   • Task: Fetch Q2 profit
   • Function: HLOOKUP
3. Inventory Lookup
   • Task: Search price by product name
   • Function: XLOOKUP

Explore more on on these topics from Microsoft’s documentation on VLOOKUP, HLOOKUP, XLOOKUP.

📆 Tips & Tricks

• Use FALSE in VLOOKUP/HLOOKUP to ensure an exact match.
• Sort your data if using approximate match (TRUE).
• Use IFERROR or IFNA to handle missing data:

=IFERROR(VLOOKUP(105, A2:D5, 3, FALSE), "Not Found")

• Use named ranges for cleaner formulas.
• Use XLOOKUP for dynamic dashboards and modern Excel features.

🌟 Summary

🎉 Pro Tip: Embrace XLOOKUP to future-proof your spreadsheets and save time.