XLOOKUP Function in Excel
Q1: Why XLOOKUP formula is better?
Answer:
XLOOKUP is better in the following ways:
- Cool to type
- Gives Reference as output.
- XLOOKUP makes the most used formula in Excel easy to use.
Q2: What are the optional parameters in the XLOOKUP formula?
Answer:
By default, Excel has only three parameters which are as follows:
In simple language :
=XLOOKUP(what you want to look, lookup list, result list)
Other than these three parameters you can also use the 4th, 5th, and 6th parameters to specify more about your LOOKUP value.
4th Parameter (If not found): This resolves the error problem.
For example :
=XLOOKUP(“Ravi”,Student[Student name],Student[total[],”Value not found”)
It will return Value not found instead of error if the lookup value is not available in the search column (student name).
5th Parameter: Match mode or type. This tells Excel how you want your match.
By default (0): Exact Match
(-1): Exact match or next smaller value.
(1): Exact match or next Larger value.
(2): Wild card character match.
6th Parameter: Match Direction
By default, the direction is top-down. Use this if you want to search from bottom to top.
Q3: What is the issue with XLOOKUP function?
Answer:
The issue with the XLOOKUP function is that it is not backward compatible. That means if you use XLOOKUP in a particular file and open it in a version that does not have XLOOKUP function then it will show an error.
Q4: How to copy the XLOOKUP formula in Multiple Cells?
Answer:
While copying an XLOOKUP formula to multiple cells, Lock the LOOKUP or return ranges with Absolut cell reference to prevent them from changing.
XLOOKUP Function in Excel With Examples
XLOOKUP Function helps us to search value in a horizontal or vertical dataset and return the relative value in some other row or column. In this article, we will look at the XLOOKUP Function in Excel. XLOOKUP is a modern replacement for functions like HLOOKUP, VLOOKUP, and LOOKUP. XLOOKUP supports approximate and exact matching, wildcards for partial matches, and lookups in horizontal or vertical ranges. XLOOKUP can be performed in Microsoft 365.
Contact Us