An IF formula result that based on multiple criteria

**johnfash** · 05-15-2013, 06:15 AM

Hi all,

Long time fan of the forum, first time poster. Majority of problems I have are resolved from someone else's query. This one I'm sure is a simple fix but I cannot get the result!

I want to search multiple rows (using an IF formula) for certain text items, being individuals initials. If the rows contain one of the 5 initials, the output should be 1 or 0 if false.

Sounds simple and I know I'm going to kick myself when I see the result!

Thanks in advance.

JF

**djauncey** · 05-15-2013, 06:25 AM

Hi John,

If you can post an example doc then it would be much easier to explain

**johnfash** · 05-15-2013, 06:54 AM

Hi,

Sorry but my employer set restrictions on cloud services so I wouldn't be able to upload the example.

I'll try to explain it in the way that I want the formula to work:

If cells from row A1 to A1000 have "AA" or "AB" or "AC", then cell B1 = "1" if true and "0" if false.

Thanks,

JF

**ARGK** · 05-15-2013, 08:07 AM

Hi
Paste this formula into Cell B1 and copy down your data as far as you need. It will return TRUE or FALSE:
=OR(A1="AA",A1="AB",A1="AC")
Hope this helps.
Tony

**Tony Valko** · 05-15-2013, 08:15 AM

Enter the initials in a horizontal range of cells:

C2: AA
D2: AB
E2: AC

Then, use this array formula**:

=--OR(A2:A100=C2:E2)

** array formulas need to be entered using the key
combination of CTRL,SHIFT,ENTER (not just ENTER).
Hold down both the CTRL key and the SHIFT key
then hit ENTER.