Thread: How to cross reference two criteria to get up to 12 different results?

Hi,

I am putting together a RAG rating system to denote a cost x number of services as Red, Amber or Green.

There are 4 possible cost brackets x 3 possible numbers of services meaning that potentially there could be 12 different results (however, I have limited the results to 3 - RED AMBER GREEN.

Please see attached example. I am looking for a formula which will look at the value in column C and number of services in column D and calculate the result in E.

Thanks and regards

MG

Try perhaps:

=LOOKUP(INDEX($D$6:$F$9,MATCH(TRUE,INDEX($C$6:$C$9<=C15,0),0),MATCH(D15,$D$5:$F$5)),{"A","G","R"},{" Amber","Green","Red"})

Yes, you are correct, {"A","G","R"}.{"Amber","Green","Red"})) is part of the LOOKUP() function and basically translates the A,G,R to the respective full text colour names.... If you simply want the A,G or R displayed, then no need for the LOOKUP() function....

=INDEX($D$6:$F$9,MATCH(TRUE,INDEX($C$6:$C$9<=C15,0),0),MATCH(D15,$D$5:$F$5))