IF Conditional formatting with more conditions

temakhafola

New Member
Joined
Apr 13, 2016
Messages
19
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".
 

Excel Facts

Excel Joke
Why can't spreadsheets drive cars? They crash too often!
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?
 
Upvote 0
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.
 
Upvote 0
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?
 
Upvote 0
Hi,

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

Put the following information in to TableData tab like such:

ABCDEF
1ColA OptionsColB OptionsColC ResultsColD ResultsColE ResultsColF Results
2DamagedUsableResult here...Result here...Result here...Result here...
3Option 2UsableResult here...Result here...Result here...Result here...
4Option 3UsableResult here...Result here...Result here...Result here...
5Option 4UsableResult here...Result here...Result here...Result here...
6Option 5UsableResult here...Result here...Result here...Result here...
7Option 6UsableResult here...Result here...Result here...Result here...
8Option 7UsableResult here...Result here...Result here...Result here...
9Option 8UsableResult here...Result here...Result here...Result here...
10Option 9UsableResult here...Result here...Result here...Result here...
11Option 10UsableResult here...Result here...Result here...Result here...
12Option 11UsableResult here...Result here...Result here...Result here...
13DamagedNon-UsableReturn t Vendor - RepairsHeld For DisposalScrappingReturn to Vendor
14Option 2Non-UsableResult here...Result here...Result here...Result here...
15Option 3Non-UsableResult here...Result here...Result here...Result here...
16Option 4Non-UsableResult here...Result here...Result here...Result here...
17Option 5Non-UsableResult here...Result here...Result here...Result here...
18Option 6Non-UsableResult here...Result here...Result here...Result here...
19Option 7Non-UsableResult here...Result here...Result here...Result here...
20Option 8Non-UsableResult here...Result here...Result here...Result here...
21Option 9Non-UsableResult here...Result here...Result here...Result here...
22Option 10Non-UsableResult here...Result here...Result here...Result here...
23Option 11Non-UsableResult here...Result here...Result here...Result here...

<tbody>
</tbody>

(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:
Code:
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)

ABCDEF
1DamagedNon-UsableReturn t Vendor - RepairsHeld For DisposalScrappingReturn to Vendor

<tbody>
</tbody>


Hope this makes sense!
 
Last edited:
Upvote 0
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
 
Upvote 0
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
 
Upvote 0

Forum statistics

Threads
1,213,486
Messages
6,113,932
Members
448,533
Latest member
thietbibeboiwasaco

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
Back
Top