What XLOOKUP does
XLOOKUP searches for a value in one column and returns a corresponding value from another column – from the same row. Syntax:
=XLOOKUP(lookup_value, lookup_array, return_array)
Typical question: "I have a student ID – give me the name." Or: "I have a class code – give me the form teacher."
Simple example
Sheet 1 column A contains student IDs, column B contains names. Sheet 2 column A contains IDs from a test result list. Add the name in column B:
=XLOOKUP(A2, Students[ID], Students[Name])
Result: Excel looks up A2 in the ID column of the Students table and returns the name from the same row.
Optional parameters
XLOOKUP has three optional parameters: =XLOOKUP(lookup_value, lookup_array, return_array, [if_not_found], [match_mode], [search_mode])
- if_not_found: text or value to return when nothing is found, e.g.
"not found"instead of the default #N/A error - match_mode: 0 = exact match (default), -1 = next smaller, 1 = next larger
- search_mode: 1 = first to last (default), -1 = last to first
The most useful optional parameter is if_not_found:
=XLOOKUP(A2, Students[ID], Students[Name], "not found")
Why XLOOKUP is better than VLOOKUP
- No column number needed: VLOOKUP requires a fixed column index (e.g. 3 for the third column). XLOOKUP references the return column directly – much more readable and easier to maintain when columns are inserted or reordered.
- Can search left: VLOOKUP can only return values to the right of the search column. XLOOKUP works in any direction.
- Better error handling: built-in if_not_found parameter avoids the need for IFERROR wrappers.
Practical use cases in school
- Enriching test results: add student names, classes or year groups from a master list
- Timetable lookups: find the teacher for a given class and time slot
- Cross-list matching: link any two lists that share a common ID or key value
- Grade thresholds: with match_mode -1, look up which grade band a score falls into
Key takeaway
Use XLOOKUP wherever you would have used VLOOKUP. It is more robust, easier to read and works for all search directions. The basic three-argument form covers most situations.