Index Match Formula with Multiple Results

[Sadleraj]

Hi Team,

I am trying to create an index/match formula that returns the information from matching cells.
I can get the formula to return the first occurance but none after that. Ideally i'd like all the results in the same cell.

As you can see on the attached I am wanting the rating matrix to look through the register for a match of the two conditions and return the risk ID.
Cell P6 is working to do this but is only returning the first instance (Risk ID 3).

Does anyone know if this is possible?

[alansidman]

Here is a udf that will do what you want.

[Sadleraj]

Hi Alan,

Thank you for providing that solution however it doesn't do what i need.
It is only matching against one criteria when I have two that need to match.
E.g I want the risk ID of all instances where Likely & Major have been selected in the register

Is this possible with a formula?

[alansidman]

I believe that you will need a VBA solution. If that is satisfactory to you, post back with your thoughts.

[FlameRetired]

Since you have TEXTJOIN in your upload I will assume you have Office 365 and not just the desktop 2016.

Please try this

Please Login or Register  to view this content.