Find In Set
 

How can I do:

If A1 is in Set ["Joe", "Bill", "Clare", ""] then B1 = A1, else "Not Found"?

If A1 is "Bill" then B1 should be set to "Bill".
If A1 is "George", then B1 is "Not Found"

What I am trying to do is condense a potentially large conditional.

Thanks much!

Find In Set
 

You could put this in B1:

=IF(A1="","",IF(ISNA(MATCH(A1,{"Joe","Bill","Clair e"},0)),"Not
Found",A1))

If you have a lot more names then rather than make the formula
enormous you can put the names somewhere on your sheet, eg X1:X50, and
then the formula would become:

=IF(A1="","",IF(ISNA(MATCH(A1,X$1:X$50,0)),"Not Found",A1))

Hope this helps.

Pete

On Jan 24, 12:48*am, Rod wrote:
How can I do:

If A1 is in Set ["Joe", "Bill", "Clare", ""] then B1 = A1, else "Not Found"?

If A1 is "Bill" then B1 should be set to "Bill".
If A1 is "George", then B1 is "Not Found"

What I am trying to do is condense a potentially large conditional.

Thanks much!

Find In Set
 

=IF(OR(A1={"Bill","Clare","Joe"}),A1,"Not Found")


"Rod" wrote:

How can I do:

If A1 is in Set ["Joe", "Bill", "Clare", ""] then B1 = A1, else "Not Found"?

If A1 is "Bill" then B1 should be set to "Bill".
If A1 is "George", then B1 is "Not Found"

What I am trying to do is condense a potentially large conditional.

Thanks much!

Find In Set
 

Hi,

Excel does not have an IN function like the one in Access. In addition I'm
not sure what you are trying to do with the 4th argument, but it looks like
you want B1 to show as empty if A1 is empty.

If you use
=IF(OR(A2={"Joe","Bill","Clare",""}),A2,"Not Found")
and A2 is empty then Excel returns 0. If that is acceptable great, but if
you want it to return a blank you could modify this to
=IF(A2="","",IF(OR(A2={"Joe","Bill","Clare",""}),A 2,"Not Found"))

If your example were really the data you are using and you were not
interested in balnks than here is a rather cute trick:
=IF(ISERR(FIND(A2,"JoeBillClare")),"not found",A2)



--
If this helps, please click the Yes button

Cheers,
Shane Devenshire


"Rod" wrote:

How can I do:

If A1 is in Set ["Joe", "Bill", "Clare", ""] then B1 = A1, else "Not Found"?

If A1 is "Bill" then B1 should be set to "Bill".
If A1 is "George", then B1 is "Not Found"

What I am trying to do is condense a potentially large conditional.

Thanks much!

Find In Set
 

you want it to return a blank you could modify this to
=IF(A2="","",IF(OR(A2={"Joe","Bill","Clare",""}),A 2,"Not Found"))

Elegant solution:
=IF(OR(A2={"Joe","Bill","Clare",""}),T(A2),"Not Found")


"Shane Devenshire" wrote:

Hi,

Excel does not have an IN function like the one in Access. In addition I'm
not sure what you are trying to do with the 4th argument, but it looks like
you want B1 to show as empty if A1 is empty.

If you use
=IF(OR(A2={"Joe","Bill","Clare",""}),A2,"Not Found")
and A2 is empty then Excel returns 0. If that is acceptable great, but if
you want it to return a blank you could modify this to
=IF(A2="","",IF(OR(A2={"Joe","Bill","Clare",""}),A 2,"Not Found"))

If your example were really the data you are using and you were not
interested in balnks than here is a rather cute trick:
=IF(ISERR(FIND(A2,"JoeBillClare")),"not found",A2)



--
If this helps, please click the Yes button

Cheers,
Shane Devenshire


"Rod" wrote:

How can I do:

If A1 is in Set ["Joe", "Bill", "Clare", ""] then B1 = A1, else "Not Found"?

If A1 is "Bill" then B1 should be set to "Bill".
If A1 is "George", then B1 is "Not Found"

What I am trying to do is condense a potentially large conditional.

Thanks much!