IF Conditional formatting with more conditions

[temakhafola]

I need a formula that I will use based on column A1 and B1. I'll need to put formula's in column C1, Column D1, ColumnE1 and Column F1 and all these formulas are dependent on each other (e.g. A1 = Damaged, B1 = Non-Usable then C1 must return "Return t Vendor - Repairs", D1 must return "Held For Disposal", E1 must return "Scrapping" and F1 must return "Return to Vendor".

 

[tyija1995]

Hi,

There's probably more to this but for what you have described use:

C1:
=IF(AND(A1="Damaged",B1="Non-Usable"),"Return t Vendor - Repairs","PH")

D1:
=IF(AND(A1="Damaged",B1="Non-Usable"),"Held For Disposal","PH")

E1:
=IF(AND(A1="Damaged",B1="Non-Usable"),"Scrapping","PH")

F1:
=IF(AND(A1="Damaged",B1="Non-Usable"),"Return to Vendor","PH")

Now my question is, how many combinations of answers can you have in A1 & B1 that will determine the output of C1:F1?

 

[temakhafola]

I have 11 options in Column A and 2 Options in Column B

 

[tyija1995]

Is column B dependent on column A or not?

If not then there will be 22 possible combinations, I'd make some sort of table which details which results you want in columns C:F

E.g.: What if Column A is option 8 and Column B is option 2? What do you want to see in Cols C:F ?

Once a table has been created you can reference it - an Nth Index Match would work and should be easy to construct based on Column B only having 2 values.

 

[temakhafola]

In Column A I will have the 11 options and in Column B I'll have a drop down arrow of the 2 options which are Usable or Non-Usable. How do I now construct the formulas from column C to F with conditions based on Column A and the answer in Column B?

 

[tyija1995]

Hi,

For this example I'd make a new tab called "TableData"

Put the following information in to TableData tab like such:

[TABLE="class: grid, width: 750"]
<tbody>[TR]
[TD][/TD]
[TD]A[/TD]
[TD]B[/TD]
[TD]C[/TD]
[TD]D[/TD]
[TD]E[/TD]
[TD]F[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]ColA Options[/TD]
[TD]ColB Options[/TD]
[TD]ColC Results[/TD]
[TD]ColD Results[/TD]
[TD]ColE Results[/TD]
[TD]ColF Results[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]Damaged[/TD]
[TD]Usable[/TD]
[TD]Result here...[/TD]
[TD]Result here...[/TD]
[TD]Result here...[/TD]
[TD]Result here...[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]Option 2[/TD]
[TD]Usable[/TD]
[TD]Result here...[/TD]
[TD]Result here...[/TD]
[TD]Result here...[/TD]
[TD]Result here...[/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD]Option 3[/TD]
[TD]Usable[/TD]
[TD]Result here...[/TD]
[TD]Result here...[/TD]
[TD]Result here...[/TD]
[TD]Result here...[/TD]
[/TR]
[TR]
[TD]5[/TD]
[TD]Option 4[/TD]
[TD]Usable[/TD]
[TD]Result here...[/TD]
[TD]Result here...[/TD]
[TD]Result here...[/TD]
[TD]Result here...[/TD]
[/TR]
[TR]
[TD]6[/TD]
[TD]Option 5[/TD]
[TD]Usable[/TD]
[TD]Result here...[/TD]
[TD]Result here...[/TD]
[TD]Result here...[/TD]
[TD]Result here...[/TD]
[/TR]
[TR]
[TD]7[/TD]
[TD]Option 6[/TD]
[TD]Usable[/TD]
[TD]Result here...[/TD]
[TD]Result here...[/TD]
[TD]Result here...[/TD]
[TD]Result here...[/TD]
[/TR]
[TR]
[TD]8[/TD]
[TD]Option 7[/TD]
[TD]Usable[/TD]
[TD]Result here...[/TD]
[TD]Result here...[/TD]
[TD]Result here...[/TD]
[TD]Result here...[/TD]
[/TR]
[TR]
[TD]9[/TD]
[TD]Option 8[/TD]
[TD]Usable[/TD]
[TD]Result here...[/TD]
[TD]Result here...[/TD]
[TD]Result here...[/TD]
[TD]Result here...[/TD]
[/TR]
[TR]
[TD]10[/TD]
[TD]Option 9[/TD]
[TD]Usable[/TD]
[TD]Result here...[/TD]
[TD]Result here...[/TD]
[TD]Result here...[/TD]
[TD]Result here...[/TD]
[/TR]
[TR]
[TD]11[/TD]
[TD]Option 10[/TD]
[TD]Usable[/TD]
[TD]Result here...[/TD]
[TD]Result here...[/TD]
[TD]Result here...[/TD]
[TD]Result here...[/TD]
[/TR]
[TR]
[TD]12[/TD]
[TD]Option 11[/TD]
[TD]Usable[/TD]
[TD]Result here...[/TD]
[TD]Result here...[/TD]
[TD]Result here...[/TD]
[TD]Result here...[/TD]
[/TR]
[TR]
[TD]13[/TD]
[TD]Damaged[/TD]
[TD]Non-Usable[/TD]
[TD]Return t Vendor - Repairs[/TD]
[TD]Held For Disposal[/TD]
[TD]Scrapping[/TD]
[TD]Return to Vendor[/TD]
[/TR]
[TR]
[TD]14[/TD]
[TD]Option 2[/TD]
[TD]Non-Usable[/TD]
[TD]Result here...[/TD]
[TD]Result here...[/TD]
[TD]Result here...[/TD]
[TD]Result here...[/TD]
[/TR]
[TR]
[TD]15[/TD]
[TD]Option 3[/TD]
[TD]Non-Usable[/TD]
[TD]Result here...[/TD]
[TD]Result here...[/TD]
[TD]Result here...[/TD]
[TD]Result here...[/TD]
[/TR]
[TR]
[TD]16[/TD]
[TD]Option 4[/TD]
[TD]Non-Usable[/TD]
[TD]Result here...[/TD]
[TD]Result here...[/TD]
[TD]Result here...[/TD]
[TD]Result here...[/TD]
[/TR]
[TR]
[TD]17[/TD]
[TD]Option 5[/TD]
[TD]Non-Usable[/TD]
[TD]Result here...[/TD]
[TD]Result here...[/TD]
[TD]Result here...[/TD]
[TD]Result here...[/TD]
[/TR]
[TR]
[TD]18[/TD]
[TD]Option 6[/TD]
[TD]Non-Usable[/TD]
[TD]Result here...[/TD]
[TD]Result here...[/TD]
[TD]Result here...[/TD]
[TD]Result here...[/TD]
[/TR]
[TR]
[TD]19[/TD]
[TD]Option 7[/TD]
[TD]Non-Usable[/TD]
[TD]Result here...[/TD]
[TD]Result here...[/TD]
[TD]Result here...[/TD]
[TD]Result here...[/TD]
[/TR]
[TR]
[TD]20[/TD]
[TD]Option 8[/TD]
[TD]Non-Usable[/TD]
[TD]Result here...[/TD]
[TD]Result here...[/TD]
[TD]Result here...[/TD]
[TD]Result here...[/TD]
[/TR]
[TR]
[TD]21[/TD]
[TD]Option 9[/TD]
[TD]Non-Usable[/TD]
[TD]Result here...[/TD]
[TD]Result here...[/TD]
[TD]Result here...[/TD]
[TD]Result here...[/TD]
[/TR]
[TR]
[TD]22[/TD]
[TD]Option 10[/TD]
[TD]Non-Usable[/TD]
[TD]Result here...[/TD]
[TD]Result here...[/TD]
[TD]Result here...[/TD]
[TD]Result here...[/TD]
[/TR]
[TR]
[TD]23[/TD]
[TD]Option 11[/TD]
[TD]Non-Usable[/TD]
[TD]Result here...[/TD]
[TD]Result here...[/TD]
[TD]Result here...[/TD]
[TD]Result here...[/TD]
[/TR]
</tbody>[/TABLE]

(See the bits in red to change to your actual results and options)

Once that is set up go back to the worksheet you are putting in options for column A and B

Lets say A1 = "Damaged" and B1 = "Non-Usable"

Then use this ARRAY formula in C1:

C1:

INDEX(TableData!C$2:C$23,SMALL(IF(TableData!$A$2:$A$23=$A1,ROW(TableData!$A$2:$A$23)-ROW(INDEX(TableData!$A$2:$A$23,1,1))+1),IF($B1="Usable",1,2)))

Ensure that you enter with Ctrl+Shift+Enter instead of a regular Enter. (You will see a #VALUE ! error if you do not use Ctrl+Shift+Enter)

Copy C1 and paste in to D1:F1

Note: If you try copy C1 and paste with C1 selected it may pop up an error (You can't change part of an array.) - So make sure C1 is not selected when you paste. I.e. Select D1:F1 only then paste.

This results in: (For example given)

[TABLE="class: grid, width: 1000"]
<tbody>[TR]
[TD][/TD]
[TD]A[/TD]
[TD]B[/TD]
[TD]C[/TD]
[TD]D[/TD]
[TD]E[/TD]
[TD]F[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]Damaged[/TD]
[TD]Non-Usable[/TD]
[TD]Return t Vendor - Repairs[/TD]
[TD]Held For Disposal[/TD]
[TD]Scrapping[/TD]
[TD]Return to Vendor[/TD]
[/TR]
</tbody>[/TABLE]


Hope this makes sense!

 

[temakhafola]

Hi,
Thanks for the info. I used the above formula however I'm getting either usable or Non-usable as a result in column C

 

[temakhafola]

Hello,

Just to add something, Can Column C be a drop down adding column D to F as options? It will still be based on choices in Column A & B