In the past, when faced with lookup and match problems, the first thought that came to mind was the VLOOKUP function. However, as time goes on, VLOOKUP might become less popular due to the introduction of Excel’s new function, TEXTJOIN, which is incredibly powerful.
1. Basic Usage
The TEXTJOIN function is primarily used to concatenate text. It consists of multiple parameters:
- The first parameter is the delimiter for the text.
- The second parameter indicates whether to ignore empty values (TRUE to ignore, FALSE not to ignore).
- The third parameter is the text content to be joined, which can be a range of cells.
For example:
=TEXTJOIN(“\”, TRUE, A1:A5)
This will combine the values in cells A1 to A5, ignoring empty cells, and separate them with a backslash. If we use FALSE as the second parameter:
=TEXTJOIN(“\”, FALSE, A1:A5)
In this case, empty cells will not be ignored.
In most scenarios, we typically use TRUE for the second parameter.
2. Replacing Lookup and Match
Some might wonder how TEXTJOIN can replace VLOOKUP for matching.
For example, if we want to find salary data based on employee names, we need to use the IF function in conjunction with TEXTJOIN.
First, we can use this formula:
=IF(B:B = E2, C:C, “”)
This will find the corresponding salary in column C for the name in E2, leaving other cells blank.
By combining both functions, the complete formula becomes:
=TEXTJOIN(“”, TRUE, IF(B:B = E2, C:C, “”))
This formula will return the matching results.
3. Powerful One-to-Many Lookup
Some may argue that this isn’t much more convenient, but it becomes extremely useful in one-to-many scenarios.
For example, if we want to list all employee information based on department information, we can use the following combination of formulas:
=TEXTJOIN(“、”, TRUE, IF(A:A = E2, B:B, “”))
This formula will connect all the results that meet the condition using a Chinese comma (、), while ignoring empty cells.
Isn’t that powerful? Have you learned this function? Give it a try!
A0005
