Nested IFS with INDEX function query

Hi,

I'm looking to amend my formula in my excel worksheet which is as follows:

=INDEX('Condensed Data'!E2:E53, MATCH(1, INDEX((B6='Condensed Data'!D2:D53)*($A$6='Condensed Data'!C2:C53), 0), 0))

It returns the first value from the condensed data worksheet but I need it to return the values in priority order. For example, if supplier 1 and control 1, which has multiple controls within control 1, has 3 green and 1 red, but the red was further down the list, I would want the formula to return the result as red because red takes priority, followed by Amber and green. Only if all the results for supplier 1 and control 1 were green, should it return a green result.

I need this formula to first index match SupplierName then ControlName before then returning the prioritised result. I have tried using nested IFS but to no joy as I need the index function to first meet the criteria on SupplierName and ControlName. If I need to use VBA then happy to take suggestions on that too.

Please see attached example spreadsheet. I would be grateful for any suggestions on this.
Thanks in advance,
Craig

Try this:

Please Login or Register  to view this content.

Or:

Please Login or Register  to view this content.

Thank you Phuocam, worked a charm. Appreciate the assist