Find In Set

  • Thread starter Thread starter Rod
  • Start date Start date
R

Rod

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!
 
You could put this in B1:

=IF(A1="","",IF(ISNA(MATCH(A1,{"Joe","Bill","Claire"},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
 
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",""}),A2,"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)
 
you want it to return a blank you could modify this to
=IF(A2="","",IF(OR(A2={"Joe","Bill","Clare",""}),A2,"Not Found"))
Click to expand...

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


Shane Devenshire said:
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",""}),A2,"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 said:
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!
Click to expand...
Click to expand...
 
Back
Top