Excel Formulas: XLOOKUP, VLOOKUP, INDEX/MATCH

Mastering Lookup Functions for Smart Data Search in Excel

🗂️ Sample Dataset

Product ID	Product Name	Category	Price
101	Apple	Fruit	1.20
102	Banana	Fruit	0.50
103	Carrot	Vegetable	0.80
104	Dates	Dry Fruit	3.00
105	Eggs	Protein	2.50

🔍 1. VLOOKUP – The Classic Lookup

Example: Find the price of “Carrot”

=VLOOKUP("Carrot", B2:D6, 3, FALSE)

Result: 0.80

Note: VLOOKUP searches only left to right and breaks if columns change.

🧩 2. INDEX + MATCH – Flexible and Accurate

Example: Find the Product ID of "Dates"

=INDEX(A2:A6, MATCH("Dates", B2:B6, 0))

Result: 104

Advantage: You can look up to the left!

🌟 3. XLOOKUP – The Modern All-in-One Formula

Example: Find the Category of “Banana”, and return “Not Found” if not found

=XLOOKUP("Banana", B2:B6, C2:C6, "Not Found")

Result: Fruit

Bonus: Built-in error handling without IFERROR

📊 Comparison Table

Feature	VLOOKUP	INDEX + MATCH	XLOOKUP
Lookup Direction	Left to Right only	Any direction	Any direction
Match Type	Exact / Approximate	Exact only	Exact / Approximate
Error Handling	Needs IFERROR	Needs IFERROR	Built-in
Dynamic Columns	❌	✅	✅
Ease of Use	Easy for beginners	Medium	Easy & modern
Availability	All Excel versions	All Excel versions	Excel 365 / 2019+

✅ Summary

Task	Best Formula
Simple lookups	VLOOKUP
Lookup in either direction	INDEX/MATCH
Modern and powerful lookups	XLOOKUP

🚀 Try It Yourself!

Practice using the dataset above. Try changing the lookup value (like "Eggs") in each formula and see how the results update.