How to make index match return multiple matches

[Klitschko]

Hello,

I currently have the following formula:
=IF(COUNTIF(INDEX(I:I,MATCH((E$1&("*"&A31&"*)),H:H&J:J,0),),"red"),"red",IF(COUNTIF(INDEX(I:I,MATCH((E$1&("*"&A31&"*")),H:H&J:J,0),),"amber"),"amber","green")).

Basically,
I am trying to match cell E1 and A31 with columns H and J, respectively. If the match in column I says "red", return red. If it says amber, return amber. otherwise, return green.

The problem is Index Match only looks at the FIRST match. I have multiple matches in column I. So cell E1 and A31 can match with multiple colors .I want the "worst" color (red being the worst, then amber, then green)

 

[Klitschko]

Hello,

I currently have the following formula:
=IF(COUNTIF(INDEX(I:I,MATCH((E$1&("*"&A31&"*)),H:H&J:J,0),),"red"),"red",IF(COUNTIF(INDEX(I:I,MATCH((E$1&("*"&A31&"*")),H:H&J:J,0),),"amber"),"amber","green")).

Basically,
I am trying to match cell E1 and A31 with columns H and J, respectively. If the match in column I says "red", return red. If it says amber, return amber. otherwise, return green.

The problem is Index Match only looks at the FIRST match. I have multiple matches in column I. So cell E1 and A31 can match with multiple colors .I want the "worst" color (red being the worst, then amber, then green)

I know I can use COUNTIF INDEX SMALL ROW but idk how to do it with two criteria and I only need a partial match to A31