Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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! |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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! |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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! |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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! |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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! |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Find First Non blank cell than find column header and return that value | Excel Worksheet Functions | |||
where to put results of find operation in find and replace functio | Excel Worksheet Functions | |||
Despite data existing in Excel 2002 spreadsheet Find doesn't find | Excel Discussion (Misc queries) | |||
'find' somtimes can't find numbers. I folowd the 'help' instructi. | Excel Worksheet Functions | |||
How do I find a file/spreadsheet that Excel says is Already open but I can't find it? | Excel Discussion (Misc queries) |