Sometimes, we want to find all the matched values and combine them into one text string, i.e., we want o VLOOKUP all matched values and then combine them.
VLOOKUP all matches and combine them
I will give you two solutions. The first solution will work from Excel 2019, and the second one will work in Dynamic Array Excel. First of all, we have to check if our representative comes from the country we are looking at. Let’s take a representative from Spain. There is only one representative of Spain in our example. We can check it using the IF function, where we have to create a proper logical test. We have to take into consideration the Country column, so let’s write it by selecting the column and clicking the F4 key to lock it. Then, we have to check if the values from the column are equal to just one country (Spain) by clicking the cell with the name of Spain (D4). It means that cell D4 will be compared to each cell from the Country column individually (Fig. 1)
Fig. 1 Comparison to each cell
The logical test will return TRUE only in the case of Spain. In the case of other countries, it will return FALSE. If the logical test is in an array, we can choose proper, corresponding result as a return value. We just need to select an array of the same size, which, in our case, is the Representative column. Then, we click the F4 key to lock it. When the logical test returns FALSE, we want to see an empty text string, so we have to write a space in double quotes. Let’s close the function with a parenthesis (Fig. 2)
=IF($A$2:$A$14=D2,$B$2:$B$14,” ”)
Fig. 2 IF function
Since I’m working in Dynamic Array Excel, Excel spills the results to the whole column (Fig. 3).
Fig. 3 IF function results
We can see that we have only one representative of Spain, and many empty cells. If we want to find the remaining representatives, we have to join the information we have. To do that, we can use the TEXTJOIN function, which is available from Excel 2019. We have to give the function a delimiter. Let’s write a comma and a space in double quotes. Then, we have to decide whether we want to ignore empty cells. We definitely do, so let’s write TRUE, or its shorter version, which is 1. Then, we just have to close the whole formula once again (Fig. 4).
After entering the formula and copying it down, we have our results (Fig. 5).
Fig. 5 TEXTJOIN function results
When we are in Dynamic Array Excel, we can use the FILTER function instead of the IF function. This function just filters our data. Let’s write FILTER function. Our data is located only in one column, which is the Representative column, so we have write the range and lock it with the F4key. Then, we have to create an array of the same size with logical values of TRUE or FALSE. It means that we have to create the same logical test as we did with the IF function. Let’s add the Country column (A2:A14), F4key to lock it, then an equal sign to check whether any of the value from the Country column is equal to Spain (D2). After closing it, we have a ready formula (Fig. 6).
=FILTER($B$2:$B$14,A$2:$A$14,=D2)
Fig. 6 FILTER function
We can see that Spain has only one representative, but Poland has got three (Fig. 7).
Fig. 7 FILTER function results
That’s why we have to put our FILTER function results into the TEXTJOIN function. And, once again, we have to write a comma and a space in double quotes. Then, we have to decide that we want to ignore empty cells and close the whole formula (Fig. 8)
=TEXTJOIN(“, “,1,FILTER($B$2:$B$14,A$2:$A$14=D2))
Fig. 8 TEXTJOIN with FILTER function
We can see that the results in the first solution and in the second solution are the same (Fig. 9)
Today, we are going to combine text with two, different delimiters. In our example, we have shop and street names. We want to put a semicolon between each shop and street name. However, between street and shop names we want to put another symbol, which is a pipe. What is important in our example, is the fact that not all cells are filled. We want to just ignore them while combining text (Fig. 1).
Combine text with different delimitersFig. 1 — Text to combine
This task is very simple in Excel 2019 and newer versions because we have the TEXTJOIN function. In the first argument of this function, we are putting the delimiter. We can even put more than one delimiter. In our example, the delimiters are placed in cells D9 and E9, so let’s put them in the formula and let’s lock them by pressing the F4 key. When we put more than one delimiter in the TEXTJOIN function, the function will use them alternatively, which means that first, it will use the semicolon, then the pipe, then the semicolon and so on. In next argument we have to decide whether we want to ignore empty cells or not. In this case, as I said earlier, it’s very important for us to ignore them, so we are writing TRUE or 1. The last argument we need is just text, i.e. the range with text. Let’s close the formula (Fig. 2)
=TEXTJOIN($D$9:$E$9,1,B2:G2)
Fig. 2 — Formula for combining text
After putting it into the cell, let’s copy it down. We have the results. We can see there are semicolons between shop and street names, and pipes between street and shop names (Fig. 3).
Fig. 3 — Combined text
In the TEXTJOIN formula, delimiters orientation is not important. They can be horizontal or vertical.
We can even hardcode them here by selecting first argument and pressing the F9 key to evaluate it. Now, they are written as text (Fig. 4).
=TEXTJOIN({“;”,”|”},1,B2:G2)
Fig. 4 — Hardcode delimiters
We can leave them like that and the formula will work the same.
Today, we are going to combine or concatenate two or more texts together. Let’s start with creating full names. It is a simple example with two texts. We can use the CONCATENATE function, or, if you have Excel 2019 or a newer one, you can just use the CONCAT function which combines two text ranges. The CONCATENATE function cannot combine ranges, only single cells. However, in our example, it doesn’t matter because when we combine the first name and the last name, in reality, we want to combine three texts as there is a delimiter between the first and last name. That’s why we have to add, let’s say, a space. The space should be in double quotes because it is a text (Fig. 1).
Combine Text from Two or more Cells
=CONCATENATE(A2,” “,B2)
Fig. 1 CONCATENATE function
After entering and copying it down, we have full names (Fig. 2).
Fig. 2 Full names
If you don’t like using the function, you can use the ampersand sign (&) to combine texts. However, while using the sign, it is important to put the sign between each point of connection. It means that the & sign must be placed between the first cell address and the space, and between the space and another cell address (Fig. 3)
=A2&” “&B2
Fig. 3 Ampersand signs in a formula
After entering the formula, we have the same results as after using a function (Fig. 4). It is up to you which one you choose.
Fig. 4 The same results
In our second example, we want to combine more than one text together, i.e., the first, middle and last name. We will use the CONCATENATE function once more, but this time we are clicking on the insert function command near the formula bar and we can see that a Function Argument window appeared. In the window, we have argument/text boxes where we will write our text. We can choose a text from a cell (press Tab). In the Text2 bar, we can write a space. When we press the Tab key, Excel will add double quotes for us. In the Text3 bar there will be cell B2. In the next bar below, we do the same with writing a space. In the last bar, we place cell C2. We can even see our formula result straight in the Function Window (Fig. 5).
=CONCATENATE(A2,” “,B2,” “,C2)
Fig. 5 Function Arguments window
After entering the formula, we have our results (Fig. 6). In this example, using the Insert Function command was quite fast.
Fig. 6 Formula results
I have also prepared one more function that exists from Excel 2019. It is called TEXTJOIN. It can combine text from ranges with the same delimiter. In our case, the delimiter is a space, so we have to write a space in double quotes. Then, we have to decide if we want to ignore empty cells. In most cases yes, so we can write either TRUE or 1. Let’s write 1 because it is shorter. Then, we are selecting the text range, which is the cells that we want to join (Fig. 7)
=TEXTJOIN(“ “,1,A2:C2)
Fig. 7 TEXTJOIN function
After entering the formula and copying it down, we have our results (Fig. 8)
Fig. 8 TEXTJOIN function results
While combining many texts, it is very important to choose proper function. Let’s assume that some people don’t have middle names. In such a case, the TEXTJOIN function will ignore empty cells and will add only one space. However, the CONCATENATE function will leave two spaces. In such a situation we would have to use the IF function to properly address this problem (Fig. 9).
Fig. 9 One space and two spaces in different functions
Summing up, we joined the first, middle and last name in each row using the CONCATENATE function that combines more than two texts, and the TEXTJOIN function that combines a range of texts.
