In the past, we needed to use the VLOOKUP formula every day at work, but now the latest version of Excel has updated the XLOOKUP formula, only to find out that it is really fragrant
1. Basic usage of XLOOKUP
The basic usage of the XLOOKUP formula is:
=XLOOKUP(Find Value, Find Column, Result Column)
So when we encounter the problem of finding and matching, we only need to find these 3 data to quickly match
For example:
Find matching payroll data based on the employee's name:
The first is to find the value, which is naturally the G2 cell Zhuge Liang
The lookup column is column A of the data source
The desired result column is column D of the data source
So the formula we just need to enter is:
=XLOOKUP(G2,A:A,D:D)
You can match them all, it's very simple to use
2. Match multiple values at once
As another example, match all the information based on the name:
Again, the lookup value is the G2 cell
The lookup column is column A
But this time the result column is a multi-column data, B:E4 column, then we put it all in the 3rd parameter, and the formula used is:
=XLOOKUP(G2,A:A,B:E)
All the results were matched at once
3. Two XLOOKUP combinations
In a special case, let's say that the order of the results we need is not the same as the order of the original table
When we want to find a matching base salary
The formula we need to use is:
=XLOOKUP(G2,$A$2:$A$9,$D$2:$D$9)
Then when we want to find the matching department, we can use the formula:
=XLOOKUP(G2,$A$2:$A$9,$B$2:$B$9)
Of course, we can enter 4 formulas in order to solve the problem, but if we want to find all of them at once
Then we only need two XLOOKUP formulas to combine and do it quickly
On the other hand, when we use the formula:
=XLOOKUP(H1,$1:$1,$2:$9)
The lookup value is cell H1, base salary
The lookup area is the first row of data
The result area is the data from rows 2 to 9
This formula directly extracts all the data of the basic salary
That is to say, we can make this formula the 3rd parameter of the previous XLOOKUP formula, and that's it,
So the combination of formulas we use is:
=Slookup($G2,$A$2:$A$9,Slookup(H$1,$1:$1,$2:$9))
You can quickly match it out
Have you learned this little trick? Try it out!