Formula that checks several criteria before returning value

Notokung

New Member
Joined
Sep 18, 2006
Messages
2
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
 

Excel Facts

Convert text numbers to real numbers
Select a column containing text numbers. Press Alt+D E F to quickly convert text to numbers. Faster than "Convert to Number"

acw

MrExcel MVP
Joined
Feb 13, 2004
Messages
4,814
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

New Member
Joined
Sep 18, 2006
Messages
2
Hi Tony,

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

Thks,
Notokung
 

Domenic

MrExcel MVP
Joined
Mar 10, 2004
Messages
19,697
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

MrExcel MVP
Joined
May 15, 2003
Messages
8,638
Hi,

Try:

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

Forum statistics

Threads
1,136,712
Messages
5,677,330
Members
419,688
Latest member
sarahmichelle

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top