Concatenate values in range if value in multiple columns is true

Hey,

I am making a 5 x 5 risk Matrix. Every risk is rated With a probability score (1-5) and a consequence score (1-5) which determine their position (1-25) in the risk matrix.

I have a summary table of all the risks. The table contain the risk ID, the probability score and the consequence score. I need to concatenate all the risk ID of every risk that has the same risk coordinates in the corresponding cell in the risk Matrix.

I previously solved this With a helping Matrix that listed all the risk IDs for each risk Matrix position 1-25. But my solution was Limited to a set number of possible risks, and it is a lot of work to adjust this if the Maximum is exceeded.

Is it possible to make a formula that basically concatenate all risk IDs from the risk table, based on the probability and consequence values (coordinates) and Place them in the corresponding risk Matrix cell?


Please have a look at the Attached file.

Edit: This is for my Excel at work which is 2016. TEXTJOIN does not work in this Version.

In E7 copied across and down:

=TEXTJOIN(", ",TRUE,IF($E$29:$E$50=$D7, IF($F$29:$F$50=E$21,$C$29:$C$50,""),""))

Thank you. Unfortunately TEXTJOIN does not work With my Excel. I use Excel 2016, not 365.

Do you know a workaorund?

Well, you have put Excel (Office) 2019 in your forum profile, which is what I based my solution on!!!

Please update your user profile to Excel 2016 so that others don't end up wasting their time.

I'm sorry, I use both 2019 and 2016. 2019 is my private Version, and 2016 is the Version at work. I should have specified that in the initial post.

You should update your profile - anybody answering will look at it as a guideline, so you should show the oldest version on which you need anything to work. You can give both, as I have done in mine.

I don't have time at the moment to help further (I'm at work online), but someone else will.

Administrative Note:

Please don't quote whole posts, especially when you are responding to the one immediately preceding your own - it's just clutter. It's OK to quote if you are responding to a post out of sequence, but limit quoted content to a few relevant lines that makes clear to whom and what you are responding. Thanks!

For normal conversational replies, try using the QUICK REPLY box below or the REPLY button instead of REPLY WITH QUOTE.