XLOOKUP is a flexible function that can be used in different situations and it’s a replacement for functions such as VLOOKUP. The syntax of the XLOOKUP function is shown below:
The first 3 arguments are required and the last three are optional:
- Lookup_value – the value to search for
- Lookup_array – the array or range to search
- Return_array – the array or range to return
- [if_not_found] – where a valid match is not found, return the [if_not_found] text you supply
- [match_mode] – specify the match type: 0, -1, 1, 2. See Microsoft Support link provided below for further explanation
- [search_mode] – specify the search mode to use: 1, -1, 2, -2. See Microsoft Support link provided below for further explanation
Example:
In Table 2 below, you have a list of Game Studios and a list of Games. In cell D6, you want to search for the name of the Game Studio for the specific game input in D4 (lookup_value). In this case, you are searching for the name of the game studio for the game, Cookie Clickers. The formula to search for the name of the game studio is as follows:
=XLOOKUP (D4,M:M,L:L)
The above formula states to search for the D4 value in column M and return a value from column L in the same row.
For more examples, check out the Exceljet link provided below.
https://exceljet.net/functions/xlookup-function
Links for additional information:
https://support.microsoft.com/en-au/office/xlookup-function-b7fd680e-6d10-43e6-84f9-88eae8bf5929
Author: Joanne Chan, Senior Consultant, Murdock Martell