Learn how to effectively use Excel’s search and reference features, such as VLOOKUP, HLOOKUP, and XLOOKUP.
Managing large datasets often requires quickly finding information. Lookup and reference functions in Excel are invaluable in such situations. They enable us to easily find the needed information and automate many processes. We will start with the most well-known function, VLOOKUP, then move on to HLOOKUP and the newest function, XLOOKUP, which combines the best features of the previous two.
VLOOKUP
Description
VLOOKUP (Vertical Lookup) is a function that searches for a value in the first column of a table range and returns a value in the same row from another column.
Syntax
=VLOOKUP(lookup_value, table_array, col_index_num, [range_lookup])- lookup_value: The value to search for.
- table_array: The table range to search within.
- col_index_num: The column number to return the value from.
- [range_lookup]: Optional. TRUE (default) for approximate match, FALSE for exact match.
Example
Let’s say we have a table with customer data, where column A contains the customer ID, and column B contains the customer’s last name. We want to find the last name of the customer with ID 123.
=VLOOKUP(123, A:B, 2, FALSE)HLOOKUP
Description
HLOOKUP (Horizontal Lookup) works similarly to VLOOKUP, but it searches for a value in the first row of a table range and returns a value in the same column from another row.
Syntax
=HLOOKUP(lookup_value, table_array, row_index_num, [range_lookup])- lookup_value: The value to search for.
- table_array: The table range to search within.
- row_index_num: The row number to return the value from.
- [range_lookup]: Optional. TRUE (default) for approximate match, FALSE for exact match.
Example
Let’s say we have a table with sales data, where the first row contains months, and the second row contains sales values. We want to find the sales value for the month “March.”
=HLOOKUP("March", A1:D2, 2, FALSE)XLOOKUP
Description
XLOOKUP is the latest lookup function in Excel, combining the capabilities of VLOOKUP and HLOOKUP and adding many new features. It allows for searching ranges both vertically and horizontally and can return values from the left side (which is not possible with VLOOKUP).
Syntax
=XLOOKUP(lookup_value, lookup_array, return_array, [if_not_found], [match_mode], [search_mode])- lookup_value: The value to search for.
- lookup_array: The range to search within.
- return_array: The range to return the value from.
- [if_not_found]: Optional. The value to return if no match is found.
- [match_mode]: Optional. 0 – exact match (default), 1 – approximate match, -1 – exact match or next smaller, 2 – wildcard match.
- [search_mode]: Optional. 1 – search first to last (default), -1 – search last to first, 2 – binary search ascending, -2 – binary search descending.
Example
Let’s say we have a table with product data, where column A contains product IDs, and column B contains product prices. We want to find the price of the product with ID “P123.”
=XLOOKUP("P123", A:A, B:B, "Not Found", 0, 1)List of Basic Lookup Operations in Excel
- Vertical Lookup (VLOOKUP)
- Horizontal Lookup (HLOOKUP)
- Advanced Lookup (XLOOKUP)
- Exact vs. Approximate Match (range_lookup/match_mode argument)
- Handling Missing Values (if_not_found argument)
- Searching in Different Directions (search_mode argument)
Conclusion
Lookup and reference functions in Excel are crucial for any data analyst. VLOOKUP and HLOOKUP are basic tools that allow for quick data searches vertically and horizontally. XLOOKUP is a more advanced function that combines the best features of both earlier functions and adds many new capabilities. Mastering these functions allows for efficient data work and significantly increases productivity. I encourage you to experiment with these functions and discover their full potential in your daily work.
Other interesting articles:
- Python Basics: An Introduction for Beginners
- SQL basics: How to start your adventure with databases
- Advanced Formulas in Excel: Tricks and Practical Applications
Prefer to read in Polish? No problem!
That’s all on this topic. Analyze in peace!
Did you like this article 🙂?
Share it on Social Media 📱
>>> You can share it on LinkedIn and show that you learn something new every day.
>>> You can throw it on Facebook – and perhaps help a friend of yours who is looking for this.
>>> And remember to bookmark this page, you never know if it won’t come handy in in the future.
You prefer to watch 📺 – no problem
>>> Subscribe and watch my English channel on YouTube.
