Function XLOOKUP()
XLOOKUP function in Microsoft Excel allows users to look up and retrieve data from a table based on a specified search term.
It can perform:
- Exact match
- Approximate match
- wildcard matches.
And it can return results from a single column or multiple columns.
The XLOOKUP function has several arguments, including the lookup value, the lookup array, the return array, and optional arguments for specifying the match type and search mode.
XLOOKUP (lookup_value, lookup_array, return_array, if_not_found, match_mode, search_mode)
It can be used for a variety of tasks, such as finding a value in a table, retrieving data based on multiple criteria, and creating dynamic ranges.
| ARGUMENT | DESCRIPTION | |
| lookup_value | The value to search for | Mandatory |
| lookup_array | The range to search | Mandatory |
| return_array | The range to return | Mandatory |
| [if_not_found] | If a valid match is not found function will return the text you provide here. If no text is provided, it displays #N/A | Optional |
| [match_mode] | Specify the match mode here: (0, -1, 1 or 2) 0 – exact match. If none is found, return #N/A. This is the default. -1 – Exact match. If none is found, return the next smaller item. 1 – Exact match. If none is found, return the next larger item. 2 – A wildcard match where *, ?, and ~ have special meaning. (See the table below) |
Optional |
| Wild card character meaning: ? Any single character * Any number of characters ~ followed by *, ? or ~ |
