XLOOKUP allows you to search through a list, find what you are looking for and return a value from a corresponding list…. and some other things that VLOOKUP couldn’t do.
(To see a shorter introduction to XLOOKUP and how it compares to other LOOKUPS, see Hookups with data.
=XLOOKUP(lookup_value, lookup_array, return_array, [if_not_found], [match_mode], [search_mode])
https://support.microsoft.com/en-us/office/xlookup-function-b7fd680e-6d10-43e6-84f9-88eae8bf5929
You need to provide the lookup_value – the thing you are trying to find.
The lookup_array – where you are looking for it, and
return_array – what you want to come back once you find what you are looking for. This could be the same as the lookup_array, but way more interesting if it isn’t. As long as it is the same dimensions as the lookup_array it can be anywhere on your spreadsheet.
The ones in square brackets are optional. If it doesn’t find what you are looking for what do you want it to do? This is what if_not_found is for. If you don’t specify this, and your lookup_value is not found then it returns an error.
match_mode can be set to find the exact thing you are looking for, or the closest bigger/smaller value.
search_mode allows you to search from the start(top) or end (bottom) of your lookup_array
Simple example
Find the name in the blue cell (Bob in this case), in the red list, and return the corresponding number in the purple list.
