# Thread: Need Help Thanks:  1 Post #5344989 (1) Likes:  1 Post #5344989 (1)

1. ## Need Help

I am quite new to Excel and I am currently using Excel 2013 at work. What I am in need of is probably extremely simple for most people on this forum so I am hopeful that I can get some help with this.

What I need:

Use of IF, AND, OR formula

I have data in Column I which has 5 different items. I also have data in Column P which either has (No Value) or (Part #) the part # can be anything.
I need is a formula that I would put into Column H2 that will do the following:

Check if data in Cell I2 = "Delivery" or "Returns" if so, check if data in cell P2 = "No Value" or "" and if data in P2 = "No Value" place a 0 in cell H2 but if P2 has anything other than "No Value" return 1 in cell H2

Any help is greatly appreciated. This has taught me that I need to do way more training in Excel to be better at my job.

2. ## Re: Need Help

When you say P2 has "No Value" is that text in the cell or is the cell "" (ie empty)

3. ## Re: Need Help

Currently it shows No Value but I can have excel clear those No Value out and have it blank.

4. ## Re: Need Help

=--AND(OR(I2={"Returns","Delivery"}),P2<>"No Value")

5. ## Re: Need Help

Thank you Fluff! I racked my brain all day yesterday and most of last night and everything that I have read and watched to teach myself how to write this formula didn't look anything like the formula that you gave to me.

Again, I greatly appreciate your quick response and assistance.

6. ## Re: Need Help

Glad to help & thanks for the feedback

7. ## Re: Need Help

Hello again. One last question if you don't mind.

How would I nest the following 4 formulas into 1 cell to return a 0 if False and 1 if True:

=--AND(OR(M2={"Returns","Delivery","Archived - Claims - Returns"}),U2<>"")
=--AND(OR(M2={"Archived - Part","Part Information"}),Q2<>"",R2<>"")
=--AND(OR(M2={"Archived - Order","Order"}),V2<>"")
=IF(OR(I2=1,J2=1,K2=1),1,0)

8. ## Re: Need Help

Actually the last line would not be needed in the formula I believe since it is checking the other three columns before returning a 0 or 1 into the target cell.

=--AND(OR(M2={"Returns","Delivery","Archived - Claims - Returns"}),U2<>"")
=--AND(OR(M2={"Archived - Part","Part Information"}),Q2<>"",R2<>"")
=--AND(OR(M2={"Archived - Order","Order"}),V2<>"")

I tried this formula:
=--AND(OR(H3={"Returns","Delivery"}),Q3<>"",--AND(OR(H3={"Part Information"}),L3<>"",M3<>"",--AND(OR(H3={"Order"}),R3<>"")))

But of course it is only returning 0's when I know there should be some 1's in there when I manually check the cells.

9. ## Re: Need Help

=IF(AND(OR(M2={"Returns","Delivery","Archived - Claims - Returns"}),U2<>""),1,IF(AND(OR(M2={"Archived - Part","Part Information"}),Q2<>"",R2<>""),1,IF(AND(OR(M2={"Archived - Order","Order"}),V2<>""),1,0)))