Microsoft Excel, other than being a complex program to use simplifies a lot of work of the users and came as a replacement for similar older functions. This can be considered as the latest version or replacement of VLOOKUP, HLOOKUP, and LOOKUP. The users may use it for the purpose of calculating approximate values, partial matches, and undergoing lookups in rows and columns.
What purpose does XLOOKUP serve?
- It helps to find the approximate values in the range or table.
- You may use it to find the best/exact match of the values.
- You can search from top to bottom or bottom to top for the in XLOOKUP array range.
The Syntax of Excel XLOOKUP is XLOOKUP (lookup, lookup_array, return_array, [not_found], [match_mode], [search_mode]).
Possible arguments of the XLOOKUP function
- Lookup- using this argument, you can easily find the lookup value.
- lookup_array- using this argument in Excel, you may lookup the array or search for the ranges.
- return_array- with the help of this range or array, you can return to the same range.
- not_found- this is an optional array to find out the value or to return back if no match is found.
- match_mod- this is also an optional array which helps you to find the exact match or the approximate value. You can also find one number smaller to it or one number larger.
- search_mode- it is yet another optional array which allows you to search from the first to last.
- XLOOKUP can be used both for finding the values in both vertical as well as horizontal arrays or rows and columns.
- It will help you to return to the range where you were in case the argument is not able to find the lookup.
- In addition to this, while using the XLOOKUP array, you need to make sure that the dimensions are compatible with the return array. If not, it will return the #VALUE!.
- In case you use it in between the workbooks, both of them must be opened at that time. If it is not, XLOOKUP will return #REF!.
- In the same manner, as the INDEX function, it will return the reference as a search result of your lookup.
Now, you will be able to use XLOOKUP function in Excel. Also, you need to undergo a lot of practice to use it efficiently.
Read Also: Update Microsoft office on MAC