What is XLOOKUP function in Excel?
The XLOOKUP function is one of the Lookup & reference functions of Excel.
It searches a range or an array for a match and returns the corresponding item from a second range or array.
By default , an exact match mode is used.
We can find this function in Lookup & reference category of insert function Tab.
How to use XLOOKUP function in excel
- Click on an empty cell (like F5).
2. Click on the fx icon (or press shift+F3).
3. In the insert function tab you will see all functions.
4. Select Lookup & reference category.
5. Select XLOOKUP function
6. Then select ok.
7. In function arguments Tab, you will see XLOOKUP function.
8. Lookup value is the value to search for.
9. Table_array is the array or range to search.
10. Return_array is the array or range to return .
11. If_not_found is returned if no match is found.
12. Match_mode specifies how to match lookup_value against the values in lookup_array.
13. You will see the result in the formula result section.
=XLOOKUP(A2,A5:A14,E5:E14,"not found",0)----->>>>answer is 175
Examples of XLOOKUP function in excel
Example 1:
How to find a specific value in a table Excel?
We want to find a specific value in a table . for example the height, age, and weight of a person named “Elijah” from the following data:
=XLOOKUP(H5,A5:A14,B5:B14)----->>>>answer is 22
=XLOOKUP(H5,A5:A14,C5:C14)----->>>>answer is 198
=XLOOKUP(H5,A5:A14,D5:D14)----->>>>answer is 185
=XLOOKUP(H5,A5:A14,E5:E14)----->>>>answer is 175Example 2:
Is Xlookup vertical or horizontal in excel?
The xlookup function searches the data vertically and horizontally in the table.
=XLOOKUP(A2,A5:A14,B5:B14)----->>>>answer is 22
=XLOOKUP("Age",A5:E5,A9:E9)----->>>>answer is 22
Example 3:
How to match exact data in XLOOKUP?
The xlookup function in its default mode finds the data as exact match. In this case, the match mode is in position 0.
=XLOOKUP(A2,A5:A14,B5:B14,,0)----->>>>answer is 22
Example 4:
How to match exact data (next smaller item) in XLOOKUP?
In this case, the match mode is in position -1. If nothing is found, it will show the next smaller item.
=XLOOKUP(A2,A6:A14,B6:B14,,-1)----->>>>answer is 9%
Example 5:
How to match exact data (next larger item) in XLOOKUP?
In this case, the match mode is in position -1. If nothing is found, it will show the next larger item.
=XLOOKUP(A2,A6:A14,B6:B14,,+1)----->>>>answer is 12%
Example 6:
How to match exact data (by wild card match) in XLOOKUP?
You can find the answer by searching a part of the desired data.In the example below, we search for the last three letters of the word along with *.
=XLOOKUP("*jah",A5:A14,B5:B14,,2)----->>>>answer is 22
In the example below, we search for the first three letters of the word along with *.
=XLOOKUP("Eli*",A5:A14,B5:B14,,2)----->>>>answer is 22
In the example below, we search for the middle three letters of the word along with *.
=XLOOKUP("*ija*",A5:A14,B5:B14,,2)----->>>>answer is 22
Example 7:
How to return more than one value in XLOOKUP?
XLOOKUP can return more than one value at the same time for the same match.
=XLOOKUP(A2,A5:A14,B5:D14,,)----->>>>answer is {22,198,185}
Example 8:
How to do two way lookup or matrix lookup using xlookup?
we can use two-way XLOOKUP for searching any specific value in table.
=XLOOKUP(B2,B5:D5,XLOOKUP(B1,A6:A14,B6:D14))----->>>>answer is 22
=XLOOKUP(B1,A6:A14,XLOOKUP(B2,B5:D5,B6:D14))----->>>>answer is 22By this formula and by changing the name or any characteristic, the desired output can be achieved. for example
=XLOOKUP(B2,B5:D5,XLOOKUP(B1,A6:A14,B6:D14))----->>>>answer is 235
What is the purpose of XLOOKUP function?
It searches a range or an array for a match and returns the corresponding item from a second range or array.
What is the Return value of XLOOKUP function?
It can return any type of data.
XLOOKUP(Lookup value,Table_array,Return_array,If_not_found)=number,textHow many arguments does XLOOKUP function have?
XLOOKUP(Lookup value,Table_array,Return_array,If_not_found,Match_mode)This function has just 5 Arguments.
Lookup value is the value to search for.
Table_array is the array or range to search.
Return_array is the array or range to return.
If_not_found is returned if no match is found.
Match_mode specifies how to match lookup_value against the values in lookup_array.
Match_mode and If_not_found arguments of XLOOKUP function is optional.
Which version of Excel supports XLOOKUP function?
This function is available for just excel version 2021.
Errors in XLOOKUP function
If the VLOOKUP function does not find any items, it returns a not found (#N/A ) error.
VLOOKUP(H4,A2:E10,2,FALSE)----->>>>answer is #N/AIf the input of the function is not typed correctly, it returns a not found (#N/A ) error.
VLOOKUP(H4,A2:E10,2,FALSE)----->>>>answer is #N/AIf extra space is placed at the input, it returns a not found (#N/A ) error.
VLOOKUP(H4,A2:E10,2,FALSE)----->>>>answer is #N/AIf the table range is not entered correctly, it returns a not found (#N/A ) error.
VLOOKUP(H4,A2:E10,2,FALSE)----->>>>answer is #N/AIf the Col_index_num is not entered correctly, it returns a not found (#VALUE) error.
=VLOOKUP(H4,A2:E10,-1,FALSE)----->>>>answer is #VALUE