Excel – the XLOOKUP Function E
What is the XLOOKUP function?
The XLOOKUP function (available in Microsoft 365) searches for an item in a range of cells and returns a
corresponding piece of information from the same row as the search item. For example, you could enter a
Student ID as the search item and have the XLOOKUP return their name.
Why would I use it?
It’s a very flexible function as it can look for information anywhere in a range of cells; it can round up or
down for approximate matches; there are more choices about how the information is sorted and you can
use wildcards like * or ? in search items. Older functions like VLOOKUP were limited in where they could
look, how information was sorted and ‘broke’ if you changed the columns in the table you were searching.
XLOOKUP can also be combined with other functions in Excel to create complex formulas.
What is the syntax for it?
=XLOOKUP(lookup_value, lookup_array, return_array, [if_not_found], [match_mode], [search_mode])
• Lookup-value = the item you use for the search (e.g. StudentID)
• Lookup-array = the range of cells where you’d find the search item
• Return_array = the range of cells containing the information you’re looking for (e.g. Name)
• [if-not-found] = what you want to return if there are no results, e.g. “No result for that ID”
• [match mode] = whether you’re looking for an exact match or an approximate match
• [search_mode] = the order you want to search, e.g. top to bottom, bottom to top
Example
In cell H3, I have calculated the highest sales value of the songs listed in the table but I want to know the
name of the song, the artist and the year it was released.
Cell H4: =XLOOKUP($H$3,$E$4:$E$102,$A$4:$A$102,"No tunes",0,1)
Cell H5: =XLOOKUP($H$3,$E$4:$E$102,$B$4:$B$102,"No tunes",0,1)
Cell H6: =XLOOKUP($H$3,$E$4:$E$102,$C$4:$C$102,"No tunes",0,1)
University of Aberdeen :: Choose an item Reviewed: 09/07/2021
The University of Aberdeen is a charity registered in Scotland, No SC013683
For each formula;
− Cell H3 is the lookup_value
− The cells containing the Sales values at today’s prices (column E) are the lookup_array
− The cells containing the song titles (column A) are the return_array
− The optional [if_not_found] is “No tunes”
− The optional [match_mode] is 0 to find an exact match
− The optional [search_mode] is 1 to start searching from the top of the list
The default setting for the match mode is Exact match and the default search mode is top-to-
bottom, so I could have left them out of the formulas in the example.
For the formulas in H5:H6 (to return the Artist and Year) all that needs to change are the column references
for the return_array.
This formula structure removes two of the limitations of VLOOKUP:
− it can search for items whichever column in the table they’re located in (to the right or left)
− it adjusts automatically if columns are added or removed from the table of data you’re searching.
Settings for match_mode and search-mode
match-mode
0 = exact match
-1 = exact match or next smaller item
1 = exact match or next larger item
2 = wildcard character match
search_mode
1 = search first to last
-1 = search last to first
2 = binary search (sorted in ascending order)
-2 = binary search (sorted in descending order)
Further information and help
Use MyIT to log calls with the IT Service Desk: https://myit.abdn.ac.uk
For more information and examples of the XLOOKUP function, see the
The XLOOKUP function (Microsoft website)
wildcard-match-example (exceljet website)
