For example, on the left is the company's personnel data, including departments, names, and so on
Now it is necessary to match all the data according to the departmental conditions
Because there are multiple corresponding values, it is a classic one-to-many matching problem
1. Vlookup formula
The first reaction, we will use the VLOOKUP formula to solve it
To create a helper column, we insert a column on the far left, and the input formula is:
=COUNTIFS($C$2:C2,C2)&C2
The first parameter of COUNTIFS, the first C2 fixed reference, indicates that the count is carried out downward
So it will mark the first few occurrences of the part, respectively
In this case, column A is the only value
On the right, the manual markers 1, 2, 3, 4...
Then just need to enter the formula:
=VLOOKUP(G$1&$F 2,$A:$D,4,0)
The first parameter, concatenated with two lookup values
G1 fixes the first line of the line marker
F2 fixed column marked F column
The second parameter A: Column D data is fixed reference
The third parameter 4 indicates the result of finding the 4th column, and the fourth parameter 0 indicates the exact finding
If you want to mask the wrong value, you just need to apply an IFERROR formula:
=IFERROR (VLOOKUP (G$1&$F 2.$A:$D,4,0), ")
2、Filter新函数公式
If you are using the latest version of Excel or WPS, a new Filter function formula will be added
It can filter out multiple results at once, and the formula usage is:
=Filter(Filter result, filter condition)
So when we enter the formula: =FILTER(C:C,B:B="SELL ONE")
It indicates that the filter result is in column C, and the filter condition is that column B is in the sales section
This shortlists all those who qualify
If we want to display it horizontally, we just need to add a transpose formula:
=TOROW(FILTER(C:C,B:B="销售一部"))
If our condition is a value in a cell, we just need to enter the formula:
=TOROW(FILTER(C:C,B:B=E2))
Isn't it easier, have you learned this little trick? Try it out!