XLOOKUP in Excel
Learn how to find values in Excel tables using XLOOKUP. This lesson covers exact matching, custom not-found messages, horizontal and vertical lookups, reverse searches, and practical student and course fee examples.
1. What Is XLOOKUP?
XLOOKUP searches for a value in one range and returns the corresponding value from another range. It can search vertically or horizontally and uses exact matching by default.
For example, a trainer at CIIT Training Institute can search for a student's name and retrieve that student's marks without manually searching through every row.
Illustration: XLOOKUP finds the requested student and returns the corresponding marks.
2. XLOOKUP Syntax
=XLOOKUP(lookup_value, lookup_array, return_array, [if_not_found], [match_mode], [search_mode])
| Argument | Meaning | Example |
|---|---|---|
| lookup_value | The value you want to find. | "Samiksha" |
| lookup_array | The range in which Excel searches. | A2:A6 |
| return_array | The range from which Excel returns a value. | B2:B6 |
| if_not_found | Optional result if no match is found. | "Not Found" |
| match_mode | Controls exact or approximate matching. | 0 |
| search_mode | Controls the search direction or method. | 1 |
3. Basic XLOOKUP Example
Assume student names are in A2:A6 and marks are in B2:B6. You want to find Samiksha's marks.
| Cell | Student Name | Marks |
|---|---|---|
| Row 2 | Samadhan | 85 |
| Row 3 | Samiksha | 92 |
| Row 4 | Pradnya | 76 |
| Row 5 | Omkar | 68 |
| Row 6 | Neha | 88 |
Formula
=XLOOKUP("Samiksha",A2:A6,B2:B6)
Step-by-step explanation
"Samiksha"is the value Excel must find.A2:A6is the range containing student names.B2:B6is the range containing marks.- Excel finds Samiksha in the second data row.
- Excel returns the corresponding value from column B: 92.
4. Use a Cell Reference Instead of Typed Text
Instead of typing a student name directly into the formula, you can enter the name in another cell. This makes the lookup easier to reuse.
Suppose E2 contains the name Samiksha.
=XLOOKUP(E2,A2:A6,B2:B6)
When you change E2 to Neha, the formula returns 88. The formula itself does not need to be rewritten.
5. Handle Missing Values
If a lookup value is not found, XLOOKUP normally returns
#N/A. You can provide a custom message using
the optional fourth argument.
=XLOOKUP(E2,A2:A6,B2:B6,"Student Not Found")
If E2 contains a name that does not appear in A2:A6, Excel displays Student Not Found instead of #N/A.
6. Retrieve Course Fees
XLOOKUP is useful beyond student marks. A training institute can use it to retrieve course fees from a course list.
| Course | Fee (₹) |
|---|---|
| Advanced Excel | 8,000 |
| SQL Server | 12,000 |
| Core Java | 15,000 |
| Python | 14,000 |
Assume course names are in A2:A5, fees are in B2:B5, and D2 contains the course name to search.
=XLOOKUP(D2,A2:A5,B2:B5,"Course Not Found")
If D2 contains SQL Server, the formula returns ₹12,000. The fee values are illustrative training examples.
7. Match Modes
The match_mode argument determines how XLOOKUP matches the lookup value.
| Value | Behavior |
|---|---|
0 |
Exact match. This is the default. |
-1 |
Exact match or the next smaller item. |
1 |
Exact match or the next larger item. |
2 |
Wildcard match using supported wildcard characters. |
Example: Exact Match
=XLOOKUP(E2,A2:A6,B2:B6,"Not Found",0)
This explicitly requests an exact match.
Example: Wildcard Match
If the lookup value in E2 is a partial student name such as
Sam*, wildcard matching can find a name beginning
with Sam.
=XLOOKUP(E2,A2:A6,B2:B6,"Not Found",2)
In wildcard mode, * matches any number of characters
and ? matches one character. A tilde can be used to
escape wildcard characters when searching for them literally.
8. Search Modes and Reverse Search
The search_mode argument controls the direction in which Excel searches the lookup range.
| Value | Behavior |
|---|---|
1 |
Search from first item to last. This is the default. |
-1 |
Search from last item to first. |
2 |
Binary search on ascending-sorted data. |
-2 |
Binary search on descending-sorted data. |
Example: Find the Last Matching Record
If the same student appears multiple times in a list and you want the last matching occurrence, use reverse search.
=XLOOKUP(E2,A2:A10,B2:B10,"Not Found",0,-1)
Excel searches from the bottom of the specified lookup range toward the top and returns the corresponding value from the return range.
9. Common XLOOKUP Mistakes
| Problem | Possible Cause | Solution |
|---|---|---|
| #N/A | The lookup value is missing or does not match. | Check spelling, spaces, and the lookup range. |
| Wrong returned value | The return range does not correspond to the lookup range. | Check the row or column alignment of both ranges. |
| #NAME? | The Excel version may not support XLOOKUP or the function is misspelled. | Verify the Excel version and formula spelling. |
| Unexpected approximate match | A non-default match mode was used. | Use match_mode 0 for exact matching. |
| Incorrect binary search result | The lookup range is not sorted correctly. | Sort the data as required or use a normal search mode. |
10. Practical Exercises
Exercise 1: Student Marks
- Create a table containing student names and marks.
- Use XLOOKUP to find Samadhan's marks.
- Use a cell reference to search for Neha.
- Display Student Not Found for an unknown name.
Exercise 2: Course Fees
- Enter four course names and their fees.
- Search for SQL Server and retrieve its fee.
- Change the lookup value to Python.
- Test the formula with an invalid course name.
Exercise 3: Reverse Search
- Create a list where one student's name appears more than once.
- Assign a different score to each occurrence.
- Use search_mode -1 to return the last matching score.
- Compare the result with the default search mode.
Important Points to Remember
- XLOOKUP searches one range and returns a corresponding value from another.
- Exact matching is the default behavior.
- Use the if_not_found argument to display a custom message.
- Use cell references to make lookup formulas reusable.
- Match modes support exact, approximate, and wildcard matching.
- Reverse search finds the last matching occurrence.
- Binary search modes require appropriately sorted data.
Summary
In this lesson, you learned how XLOOKUP searches for values, returns matching results, handles missing records, and uses match and search modes. You practiced retrieving student marks and course fees using practical Excel formulas.
XLOOKUP makes data retrieval easier and more flexible. In the next topic, we will learn about INDEX and MATCH, another powerful lookup technique used in Excel.