Give me an example. The boss of
sent me a list of the company’s personnel, and then asked me to quickly summarize all employees in each department
If you simply use the vlookup function formula, =VLOOKUP(A2,D:E,2,0)
can only find the first name, and cannot find all the names.
uses the lookup function to find out
❶ insert an auxiliary column, and then enter the formula:
=IFERROR(LOOKUP (1,0/($D$1:D1=D2),$F$1:F1),"")&" "&E2
❷ Then we use the LOOKUP formula:
=LOOKUP(1,0/(D:D=A2) ,F:F)
use the above two lookup functions to easily get the result of one-to-many query.
Let’s first understand the formula of the second step
lookup. The general search formula is: =lookup(1,0/(search column=search value), result column)
For example, lookup, the operation department formula, the second parameter is used 0/(D:D=A2), first judge whether the various values of column D are the targets we are looking for, if so, the result is 0, and an error value will be obtained. Then we use 1 to fuzzy search and return to the bottom The result corresponding to 0 appears
The difference between the formula of the first step and the formula of the second step is that parameter 2 and parameter 3 are relative references
when we look for the second operation department D4, we use the lookup column above column D The result column of the data area D1:D3
is the result in F1:F3, and Zhuge Liang is found, and then add a space with the ampersand, and then the employee of the department
that appears for the second time of &E4. The same is true, if the third time appears At the time, the result obtained is the result of the previous 2 times connected to the result of this time
Have you learned to understand? Try it yourself
