Comparing text in two cells to create text in another

[gareths69]

I need to create a formula that compares the text entries in 2 cells and populates another one with a text entry (in this case red, green or amber)
This needs to compare 9 different combinations.

Cell A Cell B Then Cell C =

Low Low Green
Low Med Amber
Low High Amber
Med Low Amber
Med Med Amber
Med High Red
High Low Amber
High Med Red
High High Red

I just don't know how to compile a formula that combines 9 different conditions. Hope someone can help.

[MarvinP]

Hi gareths69 and welcome to the forum,

Find the attached where I use a lookup table to return the colors in column C.

[tigeravatar]

Alternate formula:
=INDEX({"Green","Amber","Red"},ROUNDUP((MATCH(A1,{"Low","Med","High"},0)+MATCH(B1,{"Low","Med","High"},0))/2,0))

Array formula version (confirmed with Ctrl+Shift+Enter):
=INDEX({"Green","Amber","Red"},ROUNDUP(SUM(MATCH(A1:B1,{"Low","Med","High"},0))/2,0))

[tigeravatar]

Non-array version using SumProduct instead of Sum:
=INDEX({"Green","Amber","Red"},ROUNDUP(SUMPRODUCT(MATCH(A1:B1,{"Low","Med","High"},0))/2,0))

[gareths69]

Thanks very much for the help!!