Formula that checks several criteria before returning value

[Notokung]

Hi there!

I would like a formula that checks through several levels of criteria before returning the value.
So, for example, I have the following:

1 A B C D E

2 High 50 A

3 Med 40 B

4 Low 30 C

5 Others



Column A contains Labels that I want the formula to return after checking the values in columns B and C.

Example 1:
An item has score of 50 (column B) and rating of "A" (column C). Therefore, the formula will return the label of "High" (column A) for this item.

Example 2:

An item has score of 45 and rating of "B". As the score of Therefore, the formula returns label of "Med" for this item.

Example 3:
An item has score of 50 and rating of "B". The item does have the score in the "High" level. But since it has rating of "B", the formula will return "B" for this item.

Example 4:
An item has score of 55 and rating of "C". The formula will return label of "Low" for this item.

Example 5:
An item has score of 35 and rating of "D" Since the item does not have a rating of at least "c" to be labeled as "Low". The formula will return "others"


Hope you guys understand my questions and thks in advance for yr help.
Cheers,

Notokung

 

[acw]

Hi

1) Does your example 3 really return B or should it return Med?

2) If 1 above, then doesn't this return the value from column A based on the rating and the score is not relevant?


Tony

 

[Notokung]

Hi Tony,

Yeah. you are right. Example 3 was supposed to return "Med". Sorry for mistypo.

Thks,
Notokung

 

[Domenic]

2) If 1 above, then doesn't this return the value from column A based on the rating and the score is not relevant?

1) Can you please provide the answer to the above question?

2) What should the formula return if an item has a score of 35 and a rating of "A"? Or is this not possible?

 

[fairwinds]

Hi,

Try:

=CHOOSE(MIN(MATCH(C2,{"D","C","B","A"},0),MATCH(B2,{0,30,40,50})),"Other","Low","Med","High")