| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Searching for an additional item via an IF statement
I've got the following formula in column O which is designed to find any
variance of the specified company names in column C and enters "Y", otherwise enters "N". Another company has joined since writing this, so I'd like to include them in the list. I'm aware that you can only have up to 7 IF statements, therefore how can I accommodate Company G when my formula is already maxed out? - I thought that maybe I could include an OR statement, i.e IF(ISNUMBER(SEARCH(OR("*Company A*","Company B*"... but this doesn't seem to work for me? IF(ISNUMBER(SEARCH("*Company A*",C4)),"Y",IF(ISNUMBER(SEARCH("*Company B*",C4)),"Y",IF(ISNUMBER(SEARCH("*Company C*",C4)),"Y",IF(ISNUMBER(SEARCH("*Company D*",C4)),"Y",IF(ISNUMBER(SEARCH("*Company E*",C4)),"Y",IF(ISNUMBER(SEARCH("*Company F*",C4)),"Y","N")))))) Any ideas? |
|
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Searching for an additional item via an IF statement
=IF(SUMPRODUCT(COUNTIF(C4,{"*Company A*","*Company B*","*Company
C*","*Company D*","*Company E*","*Company F*","*Company G*"}))0,"Y","N") -- --- HTH Bob (there's no email, no snail mail, but somewhere should be gmail in my addy) "Sarah (OGI)" wrote in message ... I've got the following formula in column O which is designed to find any variance of the specified company names in column C and enters "Y", otherwise enters "N". Another company has joined since writing this, so I'd like to include them in the list. I'm aware that you can only have up to 7 IF statements, therefore how can I accommodate Company G when my formula is already maxed out? - I thought that maybe I could include an OR statement, i.e IF(ISNUMBER(SEARCH(OR("*Company A*","Company B*"... but this doesn't seem to work for me? IF(ISNUMBER(SEARCH("*Company A*",C4)),"Y",IF(ISNUMBER(SEARCH("*Company B*",C4)),"Y",IF(ISNUMBER(SEARCH("*Company C*",C4)),"Y",IF(ISNUMBER(SEARCH("*Company D*",C4)),"Y",IF(ISNUMBER(SEARCH("*Company E*",C4)),"Y",IF(ISNUMBER(SEARCH("*Company F*",C4)),"Y","N")))))) Any ideas? |
|
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Searching for an additional item via an IF statement
=IF(SUM(COUNTIF(C4,"*Company
"&{"A*","B*","C*","D*","E*","F*","G*"}))0,"Y","N" ) "Sarah (OGI)" wrote: I've got the following formula in column O which is designed to find any variance of the specified company names in column C and enters "Y", otherwise enters "N". Another company has joined since writing this, so I'd like to include them in the list. I'm aware that you can only have up to 7 IF statements, therefore how can I accommodate Company G when my formula is already maxed out? - I thought that maybe I could include an OR statement, i.e IF(ISNUMBER(SEARCH(OR("*Company A*","Company B*"... but this doesn't seem to work for me? IF(ISNUMBER(SEARCH("*Company A*",C4)),"Y",IF(ISNUMBER(SEARCH("*Company B*",C4)),"Y",IF(ISNUMBER(SEARCH("*Company C*",C4)),"Y",IF(ISNUMBER(SEARCH("*Company D*",C4)),"Y",IF(ISNUMBER(SEARCH("*Company E*",C4)),"Y",IF(ISNUMBER(SEARCH("*Company F*",C4)),"Y","N")))))) Any ideas? |
|
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Searching for an additional item via an IF statement
Thank you both so much!! - that works great!
"Teethless mama" wrote: =IF(SUM(COUNTIF(C4,"*Company "&{"A*","B*","C*","D*","E*","F*","G*"}))0,"Y","N" ) "Sarah (OGI)" wrote: I've got the following formula in column O which is designed to find any variance of the specified company names in column C and enters "Y", otherwise enters "N". Another company has joined since writing this, so I'd like to include them in the list. I'm aware that you can only have up to 7 IF statements, therefore how can I accommodate Company G when my formula is already maxed out? - I thought that maybe I could include an OR statement, i.e IF(ISNUMBER(SEARCH(OR("*Company A*","Company B*"... but this doesn't seem to work for me? IF(ISNUMBER(SEARCH("*Company A*",C4)),"Y",IF(ISNUMBER(SEARCH("*Company B*",C4)),"Y",IF(ISNUMBER(SEARCH("*Company C*",C4)),"Y",IF(ISNUMBER(SEARCH("*Company D*",C4)),"Y",IF(ISNUMBER(SEARCH("*Company E*",C4)),"Y",IF(ISNUMBER(SEARCH("*Company F*",C4)),"Y","N")))))) Any ideas? |
|
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Searching for an additional item via an IF statement
You're Welcome!
"Sarah (OGI)" wrote: Thank you both so much!! - that works great! "Teethless mama" wrote: =IF(SUM(COUNTIF(C4,"*Company "&{"A*","B*","C*","D*","E*","F*","G*"}))0,"Y","N" ) "Sarah (OGI)" wrote: I've got the following formula in column O which is designed to find any variance of the specified company names in column C and enters "Y", otherwise enters "N". Another company has joined since writing this, so I'd like to include them in the list. I'm aware that you can only have up to 7 IF statements, therefore how can I accommodate Company G when my formula is already maxed out? - I thought that maybe I could include an OR statement, i.e IF(ISNUMBER(SEARCH(OR("*Company A*","Company B*"... but this doesn't seem to work for me? IF(ISNUMBER(SEARCH("*Company A*",C4)),"Y",IF(ISNUMBER(SEARCH("*Company B*",C4)),"Y",IF(ISNUMBER(SEARCH("*Company C*",C4)),"Y",IF(ISNUMBER(SEARCH("*Company D*",C4)),"Y",IF(ISNUMBER(SEARCH("*Company E*",C4)),"Y",IF(ISNUMBER(SEARCH("*Company F*",C4)),"Y","N")))))) Any ideas? |
|
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Searching for an additional item via an IF statement
List the company names in a range of cells.
F2:F7 = company names =IF(COUNT(LOOKUP(2,1/SEARCH(F2:F7,C4))),"Y","N") -- Biff Microsoft Excel MVP "Sarah (OGI)" wrote in message ... I've got the following formula in column O which is designed to find any variance of the specified company names in column C and enters "Y", otherwise enters "N". Another company has joined since writing this, so I'd like to include them in the list. I'm aware that you can only have up to 7 IF statements, therefore how can I accommodate Company G when my formula is already maxed out? - I thought that maybe I could include an OR statement, i.e IF(ISNUMBER(SEARCH(OR("*Company A*","Company B*"... but this doesn't seem to work for me? IF(ISNUMBER(SEARCH("*Company A*",C4)),"Y",IF(ISNUMBER(SEARCH("*Company B*",C4)),"Y",IF(ISNUMBER(SEARCH("*Company C*",C4)),"Y",IF(ISNUMBER(SEARCH("*Company D*",C4)),"Y",IF(ISNUMBER(SEARCH("*Company E*",C4)),"Y",IF(ISNUMBER(SEARCH("*Company F*",C4)),"Y","N")))))) Any ideas? |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Item numbers result in item description in next field in Excel | Excel Worksheet Functions | |||
| Searching, matching then searching another list based on the match | Excel Discussion (Misc queries) | |||
| Identifying an item in one col/row and searching an entire spreads | Excel Worksheet Functions | |||
| Searching for a criteria in array of cells within an IF statement | Excel Worksheet Functions | |||
| Selecting an Item from a List and getting a different item to pop. | Excel Worksheet Functions | |||
Linear Mode
