PROVIDING ACCURATE, RELIABLE AND STRAIGHT TO THE POINT KNOWLEDGE ABOUT DIFFERENT IT TOOLS EXPLAINED WITH PRACTICAL EXAMPLES
XLOOKUP FUNCTION is a new function introduced in some of the EXCEL OPTIONS, such as EXCEL 365, EXCEL FOR ANDROID ETC.
We make and manage large report in Excel. Many times we need to join two or more reports into a final single report but would that be possible if there are hundreds of lines in all the three reports.
The Answer is NO!!!
But thanks to excel, which has given us many ways to help joining of such reports. Two of these options are HLOOKUP AND VLOOKUP. And one more very powerful option, which is similar to VLOOKUP but have many additional functions is XLOOKUP. (But available in few MICROSOFT OPTIONS only)
THIS ARTICLE DEALS WITH XLOOKUP.
XLOOKUP helps to retrieve an array of values from a table. It is a way in Excel to find out and extract a set of values by matching any value of the table.
THERE ARE A FEW PREREQUISITES WHICH WILL ENABLE YOU TO UNDERSTAND THIS FUNCTION IN A BETTER WAY.
The syntax ( the way how formula is phrased for excel) of XLOOKUP is
=XLOOKUP(VALUE TO BE FOUND , LOOKUP TABLE , RETURN ARRAY , ERROR MESSAGE , MATCH MODE , SEARCH MODE )
VALUE TO BE FOUND The value to be found and matched
LOOKUP TABLE The range of cells, where we will try to find the match
RETURN ARRAY The returning array or a set of values to be returned after the match has been made
ERROR MESSAGE If value is not found, this message will be displayed. Its kind of error handling.
MATCH MODE
0 – Exact match. If none found, return #N/A. This is the default.
-1 – Exact match. If none found, return the next smaller item.
1 – Exact match. If none found, return the next larger item.
2 – A wildcard match where *, ?, and ~ have special meaning.
SEARCH MODE
1 – Perform a search starting at the first item. This is the default.
-1 – Perform a reverse search starting at the last item.
2 – Perform a binary search that relies on LOOKUP VALUE being sorted in ascending order. If not sorted, invalid results will be returned.
-2 – Perform a binary search that relies on LOOKUP VALUE being sorted in descending order. If not sorted, invalid results will be returned.
GYANKOSH.NET | ||||
NAME | EMAIL ID | DOB | SALARY | EXPERIENCE |
MICHAEL | [email protected] | 12.02.1990 | 12000 | 10 |
RAVI | [email protected] | 15.03.1991 | 10000 | 1 |
DANIEL | [email protected] | 14.05.2013 | 30000 | 2 |
FLORENCE | [email protected] | 18.06.2000 | 45000 | 5 |
TOM | [email protected] | 25.07.2001 | 98000 | 4 |
HARRY | [email protected] | 31.7.1993 | 65000 | 6 |
1. Place the cursor in the cell where we want to get the result. For the example, place the cursor in J14.
2. Type the formula
=XLOOKUP(I12,G5:G10,J5:J10)
3. Click ENTER and the result will appear as 30000 , WHICH IS THE SALARY OF DANIEL.
The formula used is
=XLOOKUP(I12,G5:G10,J5:J10)
GYANKOSH.NET | ||||
NAME | EMAIL ID | DOB | SALARY | EXPERIENCE |
MICHAEL | [email protected] | 12.02.1990 | 12000 | 10 |
RAVI | [email protected] | 15.03.1991 | 10000 | 1 |
DANIEL | [email protected] | 14.05.2013 | 30000 | 2 |
FLORENCE | [email protected] | 18.06.2000 | 45000 | 5 |
TOM | [email protected] | 25.07.2001 | 98000 | 4 |
HARRY | [email protected] | 31.7.1993 | 65000 | 6 |
1. Place the cursor in the cell where we want to get the result. For the example, place the cursor in J14.
2. Type the formula
=XLOOKUP(I12,G5:G10,J5:J10)
3. Click ENTER and the result will appear as 30000 , WHICH IS THE SALARY OF DANIEL.
The formula used is
=XLOOKUP (“MADRI”, G28:G33 , J28:J33 , “EMPLOYEE DOESN’T EXIST” )
GYANKOSH.NET | ||||
NAME | EMAIL ID | DOB | SALARY | EXPERIENCE |
MICHAEL | [email protected] | 12.02.1990 | 12000 | 10 |
RAVI | [email protected] | 15.03.1991 | 10000 | 1 |
DANIEL | [email protected] | 14.05.2013 | 30000 | 2 |
FLORENCE | [email protected] | 18.06.2000 | 45000 | 5 |
TOM | [email protected] | 25.07.2001 | 98000 | 4 |
HARRY | [email protected] | 31.7.1993 | 65000 | 6 |
1. Place the cursor in the cell where we want to get the result. For the example, place the cursor in J54.
2. Type the formula
=XLOOKUP(I58,G51:G56,J51:K56)
3. Click ENTER and the result will appear as 10000 and 1 , WHICH ARE THE SALARY AND EXPERIENCE OF RAVI.
The formula used is
=XLOOKUP(I58,G51:G56,J51:K56)
GYANKOSH.NET | ||||
NAME | EMAIL ID | DOB | SALARY | EXPERIENCE |
MICHAEL | [email protected] | 12.02.1990 | 12000 | 10 |
RAVI | [email protected] | 15.03.1991 | 10000 | 1 |
DANIEL | [email protected] | 14.05.2013 | 30000 | 2 |
FLORENCE | [email protected] | 18.06.2000 | 45000 | 5 |
TOM | [email protected] | 25.07.2001 | 98000 | 4 |
HARRY | [email protected] | 31.7.1993 | 65000 | 6 |
1. Place the cursor in the cell where we want to get the result. For the example, place the cursor in J14.
FOR UPPER VALUE AVAILABLE
2. Type the formula
=XLOOKUP(I72,J65:J70,G65:G70,0,1,1)
3. Click ENTER and the result will appear as FLORENCE, which is obvious as the MATCHING MODE is 1, which means that it’ll find out the next higher value if matching value is not found. so salary of florence is 45000 and the answer is correct.
FOR LOWER VALUE AVAILABLE
Type the formula
=XLOOKUP(I72,J65:J70,G65:G70,0,-1,1)
4.Click ENTER and the result will appear as DANIEL, which is obvious as the MATCHING MODE is -1, which means that it’ll find out the next higher value if matching value is not found. so salary of DANIEL is 30000 and
the answer is correct.
The two function used for the UPPER VALUE ADAPTION and LOWER VALUE ADAPTION are as follows
FOR UPPER VALUE
=XLOOKUP(I72,J65:J70,G65:G70,0,-1,1)
FOR LOWER VALUE
=XLOOKUP(I72,J65:J70,G65:G70,0,-1,1)
This example shows the nested usage of XLOOKUP alongwith SUM AND LARGE FUNCTIONS.
*The table which can be copied directly to the sheet is available when you see the page in desktop or tablet.
GYANKOSH.NET | ||||
NAME | EMAIL ID | DOB | SALARY | EXPERIENCE |
MICHAEL | [email protected] | 12.02.1990 | 12000 | 10 |
RAVI | [email protected] | 15.03.1991 | 10000 | 1 |
DANIEL | [email protected] | 14.05.2013 | 30000 | 2 |
FLORENCE | [email protected] | 18.06.2000 | 45000 | 5 |
TOM | [email protected] | 25.07.2001 | 98000 | 4 |
HARRY | [email protected] | 31.7.1993 | 65000 | 6 |
The details can be seen . For the example, the table taken is small so that we can verify the working of the formula too. In practical reports table goes upto hundreds of thousands of rows. The efficacy of the formula will still be same.
This objective can be achieved with the following steps.
First of all, we need to find out the highest paid employees for which we will use the LARGE FUNCTION.
Second step is to find out the EXPERIENCE of the two found employees.
Finally the sum of both.So here are the steps.
1. Place the cursor in the cell where we want to get the result. For the example, place the cursor in J97.
2. Type the formula
=XLOOKUP(I12,G5:G10,J5:J10)
3. Click ENTER and the result will appear as 30000 , WHICH IS THE SALARY OF DANIEL.
The formula used is
=SUM(XLOOKUP (LARGE (J86:J91,1) , J86:J91 , K86:K91 ) , XLOOKUP (LARGE(J86:J91,2) , J86:J91 , K86:K91))
OTHER WAYS TO REACH THIS ARTICLE
YOU MAY LIKE