XLOOKUP vs VLOOKUP vs HLOOKUP: which Excel lookup should you learn?
Understand lookup direction, exact matches, compatibility and missing values using one practical product-price example.
For office users, commerce students and reporting teams
Use XLOOKUP for flexible lookups in supported Excel versions, VLOOKUP for vertical tables and HLOOKUP for horizontal tables. For product IDs and other identifiers, use an exact match and check missing or duplicate keys. Learn the data structure and compatibility before choosing the formula.
A lookup connects records through a shared key
Suppose an order contains a product ID and you need its price from a product list. The shared key is the product ID. A lookup is useful only if that key is consistent across both tables. A space, a number stored as text or two products with the same ID can produce a missing or misleading result.
Keep a separate master list with one row per product. Check duplicates before applying the formula to hundreds of orders. A formula returning a value does not prove the underlying match is the intended one.
Choose the function that fits your workbook
The following comparison concerns ordinary exact-match lookups. More advanced approximate-match cases need their own rules and data checks. Microsoft notes that XLOOKUP is unavailable in Excel 2016 and Excel 2019; check the version used by the person receiving the workbook.
| Function | Typical layout | Important habit |
|---|---|---|
| VLOOKUP | Key in first column; return from a column to its right | Use FALSE for an exact identifier match. |
| HLOOKUP | Key in first row; return from a row below | Use FALSE for an exact identifier match. |
| XLOOKUP | Separate lookup and return ranges | Exact match is the default; handle missing values. |
Try a tiny example before using real records
Put P101 and P102 in cells A2:A3, and 80 and 60 in B2:B3. Put P102 in E2. These examples should both return 60 in a compatible workbook. The dollar signs keep the source range fixed when you copy the formula.
VLOOKUP: =VLOOKUP(E2,$A$2:$B$3,2,FALSE) XLOOKUP: =XLOOKUP(E2,$A$2:$A$3,$B$2:$B$3,"Check product ID")
- Change E2 to P999 and inspect the missing-match behaviour.
- Repeat a product ID with a different price and investigate the ambiguity.
- Copy the formula down and confirm the reference stays correct.
- Open the workbook in its intended Excel version before handover.
Understand why a correct-looking answer can still be wrong
A duplicate key can return one matching record when you expected a unique product. An approximate match can also be unsuitable for an identifier. Do not hide every error with an empty string: the report may then silently omit information. Use a visible exception label and resolve exceptions before publishing totals.
If prices change over time, a current product list may not represent the price agreed on an earlier order. Decide whether your task needs today’s price or a historical transaction price. This is a business rule, not something a lookup function can decide for you.
Learn beyond the formula
After this exercise, practise INDEX/MATCH, SUMIFS, tables and PivotTables. They solve different reporting needs. DigiOffice Pro includes the lookup topics alongside broader data-quality and reporting lessons. When you can explain the key, the matching rule and the validation, you are ready to use the formula more confidently in everyday work.
Common questions
Should beginners skip VLOOKUP because XLOOKUP exists?
No. You may maintain older workbooks or collaborate with people using versions without XLOOKUP. Learn the purpose and limitations of both.
Why does my lookup return the wrong price?
Check duplicates, approximate versus exact matching, fixed ranges, data types and the business definition of the price. Verify a few matches manually before trusting the report.
Sources & further reading
Official references checked on 2 October 2026. The practice examples and learning advice in this article are original eOS Master editorial content.
- Microsoft: XLOOKUP function ↗Exact-match default and Excel version compatibility.
- Microsoft: VLOOKUP function ↗Vertical lookup syntax and matching options.
