Complicated multi part IF formula

This is the written out version of what I need converted to Excel speak. Don't think it possible in one formula, so may be looking at a series of steps.

If B2 > 0 enter "In stock" in C2. If B2 = 0 and A2 appears in column G, enter "Discontinued" in C2. If B2 = 0 and A2 appears in column H, enter "Preorder" in C2. If B2 = 0 and A2 does not appear in either columns G or H, enter "Out of stock" in C2.

Can anyone help me make this happen?

Thanks in advance for any assistance!
Denise

I took it as the value in A2 could appear anywhere in columns G or H so I came up with this

Please Login or Register  to view this content.

Hope it helps

That is correct. A2 could appear anywhere in columns G or H.

Hey Philb1 - Thanks for much for your help! I love learning new code.

=IF(B2<>"","In Stock",

IF(AND(B2="",ISERROR(INDEX($H:$H,MATCH($A$2,$H:$H,0))),ISERROR(INDEX($G:$G,MATCH($A$2,$G:$G,0)))),"Out Of Stock",

IF(AND(B2="",NOT(ISERROR(INDEX($G:$G,MATCH($A$2,$G:$G,0))))),"Discontinued",

IF(AND(B2="",NOT(ISERROR(INDEX($H:$H,MATCH($A$2,$H:$H,0))))),"Preorder"))))


Ok, can you explain to me what this is doing? I understand the first line. If B2 is empty, write "In stock". This kinda doesn't work for me, because none of my cells are actually empty. I need it to see if the cell = 0 or greater than 0. But I might can work around that.

The third and fourth lines seem to say if B2 is empty AND found in column such and such, enter the appropriate text. The NOT in this sentence throws me off, but I can go with it.

I guess this is sort of a reverse function, in that NOT means it is found, and not using NOT means it isn't found? Because the second line seems to say, if B2 is empty AND NOT found in columns H and G, enter the appropriate text.

Have I got that right?

Only problem is, I have implemented this in my spreadsheet and it's only returning "In stock" and "Out of stock". Even if the other two options are what should be in the cell, that's not what's happening.

Hi bebo021999! That's awesome. Thanks so much for your reply. Your formula is really elegant and helps with the zero problem. I implemented it in my spreadsheet and anywhere I have zeros, it's only returning the Discontinued response. I'm not getting any Out of stocks or Preorders. Any ideas?

Thanks!
Denise

Thanks bebo021999! That's was it! Works perfectly now.