As an example of work, on the left is the Employee Information Table data
According to the number, all the information that follows needs to be matched at one time
At work, I often encounter this kind of problem, there are 3 formulas, which can be solved quickly, namely VLOOKUP formula, XLOOKUP formula, and Filter formula
1. Vlookup formula
If you don't want to use the 3-pass VLOOKUP formula, then we can combine the array usage to solve the problem
It is necessary to extract the results of column 2, column 3 and column 4 in the table of raw data respectively
So the formula we enter is:
=VLOOKUP(F2,A:D,{2，3，4},0)
For the first parameter, the lookup value is F2
The second parameter, the lookup data region is column A:D
For the third parameter, you need to use curly braces, and enter 3 numbers in it
In the fourth parameter, the number 0 indicates an exact lookup
Fill it down, and you'll get all the results:
2. Xlookup formula
This formula is only available in the latest version of Excel, and it is very simple to use, just fill in the first 3 parameters:
=XLOOKUP(Lookup Value, Lookup Area, Result Area)
So here, the formula that only needs to be entered is:
=XLOOKUP(F2,A:A,B:D)
The first parameter, find the value of cell F2
The second parameter is searched in column A of the data source
The third parameter, the result we want, is 3 columns, so we choose column B:D
Again, it is easy to get the results we want:
3. Filter formula
Also as a new version of the formula, it is used to find matches, and only two parameters are needed to complete it
=Filter(Filter result, filter condition)
So, here, the formula we enter is:
=FILTER(B:D,A:A=F2)
The first parameter is the data region of column B:D of the filtered results
The second parameter is the condition, which is the value equal to F2 in column A
One-time matching results:
Will you learn these 3 methods? Which one do you prefer? Try it yourself!