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.
--
rwfster


"GSnyder" wrote:

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 A1:D1 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 A1:D1 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!