How To Join Texts Using TEXTJOIN & IF Functions | Microsoft Excel

The TEXTJOIN is a new function introduced in 2019 and available in Microsoft Excel 2019, Excel 2021 and Excel for M365. The TEXTJOIN function combines the text from multiple strings or range separated by delimiter.

Let’s try to understand the usage of TEXTJOIN function using below example.

The Table A have list of users and computers assigned to them and you need to report the data to management in the format given in Table B.

Microsoft Excel | TEXTJOIN

While we can use TEXTJOIN to combine the texts , the above requirements cannot be achieved alone with TEXTJOIN as we need to combine the data for each user separately. Hence we need to use TEXTJOIN and IF functions together to achieve the desired result.

Let’s understand the TEXTJOIN function first.




Let’s see how we can join texts with this function.

The result:

The below example show how you can join the text from multiple cells into a single cell separating them by comma.

EXCEL | TEXTJOIN | Join text into single cell

Here is the result. All the values from B2:B6 combined in D2. All values were separated by comma which we given as delimiter.

EXCEL | TEXTJOIN | Join text into single cell

Now, try to understand IF function

The Excel IF function perform logical test on given condition and return one value for TRUE result and another value for a FALSE result.

In this example, we will understand how to use IF function to find a value and then return a value from given range.



Microsoft Excel - IF Function

Now, see below example. The below formula will check all cells with in A2:A6 for value “User1”. If a match found then it will return corresponding value from B2:B6. If no match found then blank value will be returned for that row which we defined in last parameter.

Microsoft Excel | IF Function

Here is the result

Microsoft Excel | IF Function

Let’s come back to our original requirements where we need the device details for each user in single cells. We will now use both TEXTJOIN and IF function to achieve the result.

This can be achieved using below formula. We have nested IF function inside TEXTJOIN. The IF function will return all matching value for a user1, which will then combined using TEXTJOIN function separated by comma.


Here is the result.


Now apply the above logic to original table to get the desired result.

The IF function will search the value in D5 in range A5:A20 and return the corresponding value from B5:B20. All the returned values will be passed to TEXTJOIN which will combines then separated by comma.


Now you can see the result in E5 for User1. To get the same result for another user, you simply need to drag or copy the formula to E6:E10.

Here is final result.

Related Posts:

Subscribe to Techuisitive Newsletter

Be the first to know about our new blog posts. Get our newsletters directly in your inbox and stay up to date about Modern Desktop Management technologies & news.

Scroll to Top