The TEXTJOIN formula takes three arguments:

Now, all we need to do is combine these training name (separated by a comma) and return it in one cell.Īnd that can easily be done using the new TEXTJOIN formula (available in Excel 2019 and Excel in Microsoft 365) Where it has the names of the training Bob has taken and blanks wherever the name was not Bob. So this part of the formula will give you an array as shown below: if it’s the same name, it returns TRUE, else it returns FALSE. It goes through each cell in the range A2:A20, and checks whether the name is the same in cell D2 or not. The logical test in the IF formula (D2=$A$2:$A$20) checks whether the name cell D2 is the same as that in range A2:A20. Let me deconstruct this formula and explain each part in how it comes together gives us the result. In column D, I have the list of unique names (from column A), and I want to quickly lookup and extract all the training that every person has done and get these in a single set (separated by a comma).īelow is the formula that will do this: =TEXTJOIN(', ',TRUE,IF(D2=$A$2:$A$20,$B$2:$B$20,''))Īfter entering the formula in cell E2, copy it for all the cells where you want the results. So if you’re using Microsoft 365 (earlier known as Office 365), you can use the methods covered in this section could look up and return multiple values in one single cell in Excel.Īnd as you will see, it’s a really simple formula.īelow I have a data set where I have the names of the people in column A and the training that they have taken in column B.Ĭlick here to download the example file and follow alongįor each person, I want to find out what training they have completed. With Microsoft 365 subscription, your Excel now has a lot more powerful functions and features that are not there in prior versions (such as XLOOKUP, Dynamic Arrays, UNIQUE/ FILTER functions, etc.) If you’re using Excel 2016 or prior versions, go to the next section where I show how to do this using VBA. Lookup and Return Multiple Values in One Cell (Using Formula)

Lookup and Return Multiple Values in One Cell (Using VBA).

Get Multiple Lookup Values in a Single Cell (without repetition).

Lookup and Return Multiple Values in One Cell (Using Formula).

Kutools for excel 2016 vba