"MATCH" or "IF/OR"

geniechua

New Member
Joined
Jun 6, 2012
Messages
22
Hi,

I have to find a solution which I'm not entirely sure if it can be done by excel.

I have a table with 11 conditions, and depending on whether the conditions are met, there will be 4 outcomes.

ABC
1Condition 1 TrueResult A
2Condition 2FalseResult A
3Condition 3FalseResult B
4Condition 4TrueResult B
5Condition 5FalseResult B
6Condition 6FalseResult B
7Condition 7FalseResult B
8Condition 8TrueResult B
9Condition 9TrueResult B
10Condition 10TrueResult C
11Condition 11FalseResult D

<tbody>
</tbody>


Here's the restrictions to be imposed:

1. If either Condition 1 or Condition 2 is true, I want Result A. Whether any other conditions are true is irrelevant.
2. If any of Condition 3 to Condition 9 is true, I want Result B. This is regardless whether Condition 10 or Condition 11 is true
3. If only Condition 10 or Condition 11 is true, I want Result C and Result D respectively.
 

Some videos you may like

Excel Facts

Best way to learn Power Query?
Read M is for (Data) Monkey book by Ken Puls and Miguel Escobar. It is the complete guide to Power Query.

Chrisdontm

Well-known Member
Joined
May 4, 2015
Messages
810
What do you want on each of those if neither one is true, you have to have an answer for false...
 

geniechua

New Member
Joined
Jun 6, 2012
Messages
22
To add, at least one Condition will definitely be true, so I shouldn't need an answer for all false?
 

geniechua

New Member
Joined
Jun 6, 2012
Messages
22

ADVERTISEMENT

They are in another sheet in the same workbook.
 

AlanY

Well-known Member
Joined
Oct 30, 2014
Messages
4,239
Office Version
  1. 365
  2. 2019
  3. 2016
Platform
  1. Windows

ADVERTISEMENT

Hi,

I have to find a solution which I'm not entirely sure if it can be done by excel.

I have a table with 11 conditions, and depending on whether the conditions are met, there will be 4 outcomes.

ABC
1Condition 1TrueResult A
2Condition 2FalseResult A
3Condition 3FalseResult B
4Condition 4TrueResult B
5Condition 5FalseResult B
6Condition 6FalseResult B
7Condition 7FalseResult B
8Condition 8TrueResult B
9Condition 9TrueResult B
10Condition 10TrueResult C
11Condition 11FalseResult D

<tbody>
</tbody>


Here's the restrictions to be imposed:

1. If either Condition 1 or Condition 2 is true, I want Result A. Whether any other conditions are true is irrelevant.
2. If any of Condition 3 to Condition 9 is true, I want Result B. This is regardless whether Condition 10 or Condition 11 is true
3. If only Condition 10 or Condition 11 is true, I want Result C and Result D respectively.

E.g.
=countif(B1:B2,true) >0 indicates at least 1 meets the condition, similar for conditions 3-9 etc.

then if you use the if() for the desired result
 

geniechua

New Member
Joined
Jun 6, 2012
Messages
22
Hi Alan,

Thanks for the tip. That has helped me progress a little.

However, I'm now stuck at a situation where both Condition 4 and Condition 10 are true, in which case, the outcome I want is Result B and not Result C. How should I set up the IF formula?
 

AlanY

Well-known Member
Joined
Oct 30, 2014
Messages
4,239
Office Version
  1. 365
  2. 2019
  3. 2016
Platform
  1. Windows
sorry i didn't have excel with me earlier.

Excel 2012
A
B
C
1Condition 1TRUEResult A
2Condition 2FALSEResult A
3Condition 3FALSEResult B
4Condition 4TRUEResult B
5Condition 5FALSEResult B
6Condition 6FALSEResult B
7Condition 7FALSEResult B
8Condition 8TRUEResult B
9Condition 9TRUEResult B
10Condition 10TRUEResult C
11Condition 11FALSEResult D

<tbody>
</tbody>
Sheet1
=OR(B4,B10) yield >0 if either condition 4 or 10 is true.

so, the final formula can be
=IF(OR(B4,B10),"Result B",IF(COUNTIF(B1:B2,TRUE)>0,"Result A",IF(COUNTIF(B3:B9,TRUE)>0,"Result B",IF(B10=TRUE,"Result C",IF(B11=TRUE,"Result D")))))
 
Last edited:

geniechua

New Member
Joined
Jun 6, 2012
Messages
22
Hi Alan,

Thanks very much for the response! That formula, unfortunately won't work cos what I really want is if any of Condition 3 to Condition 9 is true, I want Result B. This is regardless whether Condition 10 or Condition 11 is true. However, if only Condition 10 is true, I want Result C.
 

Watch MrExcel Video

Forum statistics

Threads
1,123,158
Messages
5,600,052
Members
414,357
Latest member
Gemma_R

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