Excel Formula Question or What's the best way to do this?

[mvital]

Hi All!

I want to create a formula in Excel. I thought about using the 'AND' and 'OR' logical functions. Can they be used together with an IF statement?

This is what I am trying to accomplish. I have text in cell BG. I want to look for "Red" in BG and where it is either "100" or "5000" or "under 100" in cell AI. If it is red" and is "under 100" or "100" or "5000" I want a '1' in the cell where I have my formula. This is what I attempted and it did not work.

=IF(OR(BG2:BG592="Red", BD2:BD592="100", BD2:BD592="5000", BD2:BD592="under 100"),1, 0)

So it has to be "RED" and it can be either 100 or 100 or 5000 and it is is either of those and it is red I should see a one in my cell.

I hope that makes sense. Hope someone can help me!

thanks in advance!

 

[KenWright]

Your post is somewhat confusing. BG is not a CELL, it is a COLUMN. I can see you have two ranges to query so that I get, but are you saying you will have just one formula, and that that formula will be in cell A1, and that you want it to return a 1 if you have Red anywhere in Col BG with a matching value from your list on the same row??

OR

Are you saying you want a 1 if you have Red anywhere in Col BG and a value that matches your criteria anywhere in Col BD, not necessarily on the same row as Red?

Regards
Ken.............

----------------------------------------------------------------------------
It's easier to beg forgiveness than ask permission
----------------------------------------------------------------------------

 

[mintjulep]

And numbers work better as numbers then they do as text.