CODEWITHSUNDEEP
excel-formula
Rick Tilghman
Rick Tilghman

Reputation: 63

Excel - Need to check cell values against array and create concatenated list from secondary column

This isn't super complex, but it's got a bunch of moving parts and I'm struggling to put them all together. Have tried various fixes and solutions but can't make it work.

PROBLEM

I've got a list of values. I want to take those values (individually), look for them in a separate array, and if a match is found take the value of the cell NEXT to the match and concatenate all values in one cell.

Here's a quick example

https://i.sstatic.net/dCd2I.png

Basically I want to dynamically populate column B. To do this I need to take the ref values in column A, look for each of them in the list of "Favorite Fruits", and when found grab the name in the column next to it and concatenate the names with commas in a single field.

Thoughts? Like I said, it's not a tough problem, just a lot of moving pieces (which I guess makes it a tough problem). Any help is appreciated.

Thanks! Rick

Upvotes: 1

Views: 124

Answers (2)

jeranon
jeranon

Reputation: 432

Try putting this in Cell B2: =TEXTJOIN(", ", TRUE, IF($D$2:$D$15=A2, $E$2:$E$15,"")) make sure and lock it in as an array formula with Ctrl+Shift+Enter

Then drag/copy down to populate each needed cell.

Upvotes: 1

Jmslñ_ñ
Jmslñ_ñ

Reputation: 1

B2Formula: {=CONCAT(IF(A2=$D$2:$D$15,$E$2:$E$15 & ",",""))}

Introduce with ctrl+shift+Enter to make it matricial formula "{=Formula}" after that just drag the formula.

Upvotes: 0

Related Questions