XLOOKUP Function in Excel 365: Complete Guide with Examples and Top 20 Interview Questions

Jun 26, 2025

—

by

🔍 How to Use the XLOOKUP Function in Excel 365 — Detailed Guide

✅ What is XLOOKUP?

XLOOKUP is a powerful lookup function introduced in Excel 365 and Excel 2021 to replace older functions like VLOOKUP, HLOOKUP, and even INDEX + MATCH. It can search horizontally or vertically, supports approximate/partial matches, and even returns custom messages when no match is found.

📌 Syntax:

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

Argument	Description
`lookup_value`	The value to search for
`lookup_array`	The range or array to search in
`return_array`	The range or array to return data from
`if_not_found`	(Optional) Value to return if no match is found
`match_mode`	(Optional) 0 = exact match (default), -1 = exact or next smaller, 1 = exact or next larger, 2 = wildcard
`search_mode`	(Optional) 1 = search from first to last (default), -1 = search from last to first

🧪 Basic Example:

You have the following data:

A	B
Product	Price
Apple	100
Banana	60
Mango	80

To find the price of Mango:

=XLOOKUP("Mango", A2:A4, B2:B4)

➡️ Result: 80

🧪 Example with `if_not_found`:

=XLOOKUP("Orange", A2:A4, B2:B4, "Not Available")

➡️ Result: Not Available (because “Orange” doesn’t exist)

🧪 Example using `wildcard` match:

=XLOOKUP("*man*", A2:A4, B2:B4, , 2)

➡️ This matches any product containing “man” (e.g., “Mango”)

🧪 Reverse Lookup (Bottom to Top):

=XLOOKUP("Mango", A2:A4, B2:B4, , 0, -1)

➡️ Searches from bottom to top. Useful if the latest entry is preferred.

🧠 20 Interview-Based Questions on XLOOKUP with Answers

Q1. What is XLOOKUP in Excel?
A1. XLOOKUP is a modern lookup function that replaces older functions like VLOOKUP and HLOOKUP. It can search vertically or horizontally and offers more flexibility.

Q2. How is XLOOKUP better than VLOOKUP?
A2. XLOOKUP allows lookup to the left, supports default return on no match, wildcards, and reverse searches, which VLOOKUP cannot do.

Q3. Can XLOOKUP search horizontally?
A3. Yes. You can use it like HLOOKUP by selecting rows instead of columns.

Q4. What happens if the lookup value is not found?
A4. If you specify the if_not_found parameter, that value is returned. Otherwise, Excel returns a #N/A error.

Q5. How can you use XLOOKUP for an exact match?
A5. Either omit the match_mode (default is exact) or explicitly set it to 0.

Q6. Can XLOOKUP return an entire row or column?
A6. Yes, it supports dynamic arrays, so it can return multiple values from a row or column.

Q7. What does match_mode = 2 mean?
A7. It enables wildcard matching using * (any number of characters) or ? (single character).

Q8. What is the purpose of the search_mode parameter?
A8. It controls the search direction: 1 = top to bottom (default), -1 = bottom to top.

Q9. Is XLOOKUP case-sensitive?
A9. No, XLOOKUP is not case-sensitive by default.

Q10. Can XLOOKUP replace INDEX + MATCH?
A10. Yes, and it’s simpler to write and understand.

Q11. What’s the difference between XLOOKUP and LOOKUP?
A11. LOOKUP is an older function requiring sorted data; XLOOKUP doesn’t and is more robust.

Q12. What is returned if multiple matches are found?
A12. XLOOKUP returns the first match, unless search_mode is set to -1 (then it returns the last match).

Q13. Can XLOOKUP handle blank cells?
A13. Yes. It will match blank cells if "" is used as the lookup_value.

Q14. Can XLOOKUP be nested with other functions?
A14. Yes, it works well inside other functions like IF, SUM, etc.

Q15. How does XLOOKUP behave in arrays with errors?
A15. It stops at the first error unless error handling (like IFERROR) is added.

Q16. Is XLOOKUP available in Excel 2016 or 2019?
A16. No. XLOOKUP is only available in Excel 365 and Excel 2021.

Q17. Can XLOOKUP search from right to left?
A17. Yes, it’s not restricted by column order like VLOOKUP.

Q18. How to use XLOOKUP for range lookups (approximate match)?
A18. Set match_mode to -1 (for next smaller) or 1 (for next larger).

Q19. Can you perform two-way lookups using XLOOKUP?
A19. Yes. Combine two XLOOKUPs — one for row and one for column.

Q20. How does XLOOKUP handle dynamic named ranges or structured tables?
A20. It works seamlessly with dynamic arrays, tables, and named ranges.