"MATCH" or "IF/OR"

[geniechua]

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.

	A	B	C
1	Condition 1	True	Result A
2	Condition 2	False	Result A
3	Condition 3	False	Result B
4	Condition 4	True	Result B
5	Condition 5	False	Result B
6	Condition 6	False	Result B
7	Condition 7	False	Result B
8	Condition 8	True	Result B
9	Condition 9	True	Result B
10	Condition 10	True	Result C
11	Condition 11	False	Result 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.

 

[Chrisdontm]

What do you want on each of those if neither one is true, you have to have an answer for false...

 

[geniechua]

To add, at least one Condition will definitely be true, so I shouldn't need an answer for all false?

 

[Aladin Akyurek]

Where is the data for which these conditions must hold?

 

[geniechua]

They are in another sheet in the same workbook.

 

[Aladin Akyurek]

They are in another sheet in the same workbook.

You need to post a scaled down sample in order to prevent "unusable" replies...

 

[AlanY]

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.

	A	B	C
1	Condition 1	True	Result A
2	Condition 2	False	Result A
3	Condition 3	False	Result B
4	Condition 4	True	Result B
5	Condition 5	False	Result B
6	Condition 6	False	Result B
7	Condition 7	False	Result B
8	Condition 8	True	Result B
9	Condition 9	True	Result B
10	Condition 10	True	Result C
11	Condition 11	False	Result 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]

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]

sorry i didn't have excel with me earlier.

Excel 2012

	A	B	C
1	Condition 1	TRUE	Result A
2	Condition 2	FALSE	Result A
3	Condition 3	FALSE	Result B
4	Condition 4	TRUE	Result B
5	Condition 5	FALSE	Result B
6	Condition 6	FALSE	Result B
7	Condition 7	FALSE	Result B
8	Condition 8	TRUE	Result B
9	Condition 9	TRUE	Result B
10	Condition 10	TRUE	Result C
11	Condition 11	FALSE	Result 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")))))

 

[geniechua]

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.