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

Remove leading & trailing spaces
Save as CSV to remove all leading and trailing spaces. It is faster than using TRIM().

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
20,379
Office Version
  1. 365
Platform
  1. Windows
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,171,734
Messages
5,877,185
Members
433,237
Latest member
ala995

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