how to get IF(OR) formula to recognize multiple cells

[rwfster]

I am using the formula
=IF(OR(A5={"a","a1","a2","a3","a4","x","x1","x2","x3","x4","t","t1","t2","t3","t4","h","h1","h2","h3","h4","r","l"}),"X","")
to place an "X" in the destination cell. How would I adapt this formula to
include cells B5 through E5, and would also include the numbers 1 through 4
as part of the criteria?

 

[GSnyder]

I'd try to tackle this by using a range of values and a VLOOKUP instead of
hard coding each value. This will give you lots of flexibility for adding
and removing any of the constraints.

In this solution, I'm assuming that you mean if A1 OR B1 OR C1 OR D1 match
the criteria, then you want an 'X' to show up in E1.

Create a 2-column table in, say, H1:I50 (or however many constraints you
have) that has each of your constraints with a 1 next to it. It looks like
this:

String Value
x 1
a1 1
a2 1
a3 1
et. cetera

This table could even be on a different sheet if you want to tuckit
somewhere away.

Then, assuming your strings are in A11 that you want to check, place this
formula in E1:

=IF(IFERROR(VLOOKUP(A1,$H$2:$I$11,2,0),0)+IFERROR(VLOOKUP(B1,$H$2:$I$11,2,0),0)+IFERROR(VLOOKUP(C1,$H$2:$I$11,2,0),0)+IFERROR(VLOOKUP(D1,$H$2:$I$11,2,0),0)>0,"X","")

This will look up each of the cells A11 in the table and if it's there, it
will return a 1. If not, it errors out and returns a 0. Then it adds them
all up and if at least one of them hit, you get your X!

Hopefully, you're using Excel 2007 so the IFERROR function works, otherwise,
this thing gets ugly so you might need to split it up.

I like this better because it's flexible. You can add as many constraints
as you want in the list.

Happy calculating!

 

[Max]

One easy way is to use one helper col where you gather/list
all the possibles in the criteria to check for,
eg assume col Z is where all the criteria will be listed
Then simply use: =IF(COUNTIF(Z:Z,A5),"X","")

Works? Click YES below
--
Max
Singapore
http://savefile.com/projects/236895
Downloads:23,500 Files:370 Subscribers:66
xdemechanik

 

[rwfster]

Max, that formula works great for the A5 cell. How can it be adapted to
include evaluating cells A5 through E5? I have tried adding A5:E5 and
A5&B5&C5&D5&E5 without success. Using those parameters the destination cell
E5 is empty.

 

[rwfster]

I copied and pasted your formula then changed it to reflect 2 columns ranging
from P1:I27 but get #NAME? in the destination cell. If it matters iI am
using excell 2003.

 

[Max]

Max, that formula works great for the A5 cell

That's good. But pl click the YES in that response

How can it be adapted to include evaluating cells A5 through E5?
I have tried adding A5:E5 and A5&B5&C5&D5&E5 without success ..

I wonder if you meant to do something like this
In say F5:
=IF(SUMPRODUCT(COUNTIF(Z$2:Z$100,A5:E5)),"X","")
where Z2:Z100 contains the criteria, ie the list of the possible values, and
A5:E5 is the range to be checked for the occurence of any of these criteria
values

Adapt to suit. As-is the expression can be copied down
to return correspondingly for A6:E6, A7:E7, and so on

Celebrate success? Click YES below
--
Max
Singapore
http://savefile.com/projects/236895
Downloads:23,500 Files:370 Subscribers:66
xdemechanik
---

 

[rwfster]

works like a charm, many thanks
--
rwfster

That's good. But pl click the YES in that response


I wonder if you meant to do something like this
In say F5:
=IF(SUMPRODUCT(COUNTIF(Z$2:Z$100,A5:E5)),"X","")
where Z2:Z100 contains the criteria, ie the list of the possible values, and
A5:E5 is the range to be checked for the occurence of any of these criteria
values

Adapt to suit. As-is the expression can be copied down
to return correspondingly for A6:E6, A7:E7, and so on

Celebrate success? Click YES below
--
Max
Singapore
http://savefile.com/projects/236895
Downloads:23,500 Files:370 Subscribers:66
xdemechanik
---

 

[Max]

Welcome
--
Max
Singapore
http://savefile.com/projects/236895
Downloads:23,500 Files:370 Subscribers:66
xdemechanik