Is it possible to use an if statement with multiple conditions? I have a large database and am searching for 12 words. If any of these 12 words appears in the column in question, I would like to return TRUE. How would I go about this? Is this a nested IF statement?
I cannot put the facts you mention together sensibly. You surely are talking of a certain part of a column (or more reasonable a row.)
A column has up to 2^20-1 elements. You cannot search that huge number again and again.
IF (or the new IFS) will not help much.
You can try an “array-formula” accumulating by OR the matches that may exist. (I would prefer to count these matches by SUM.)
The attached example demonstrates the OR-ing or counting of matches for three single letters (replacing your words) in 4 adjacent cells in a row per dataset.
(Editing: Replaced first attachment with a better one.)
ask76472ArrayOfMatches_2.ods
(Editing with respect to the second comment by @AliZ below)
You are right. Neither is English my native language nor is my English actually sufficient. Nonetheless I try to understand your OQ now as "…I would like to return a TRUE per row in an extra column to allow for filtering by the results… Is this a nested IF statement? "
No. IF is not at the heart of the solution. It may occur in formulae, however.
The task as modified above is easy in a sense. @PYS and @mark_T already posted working solutions. There may be additional expectations concerning the solution, not explicitly posted by the OQ yet. …
A solution not only creating the selecting values, but also doing the filtering by formulae is attached.
ask76472ArrayOfMatches_4.ods
Hmm. Not sure how to be clearer. Maybe this example helps? I really appreciate the help, but not sure the array-formula will work. For example, I have a database with 5000+ rows. Column P lists fruits. If a cell in that column contains apples, bananas, pears, or oranges, I would like that to be replaced with TRUE. Is there a way to do this with an IF statement? Such as =IF(P1:P5000; “apples, bananas, pears, oranges”, TRUE)
I cannot imagine a solution using IF. Why?
I do not understand the new usage of the word “replace”.
(I also do not understand in what way the information you are trying to get should be useful. A real database would SELECT WHERE and count the results. An established way to mimic SQL with data collections in spreadsheets is the usage of helper columns. Anyway a more exact knowledge of the actual problem is indispensable to be able to give valuable advice.)
I’m guessing English is not your native language, so I’m sorry if my explanation is hard to grasp. I will try again, however, because you seem to want to help. (Maybe?) I need to save all of the rows that contain either “apples, bananas, pears or oranges” in Column P. I figured that if I marked all of these as TRUE, then I could easily apply a filter and get all of the Column P TRUE items. I really would just like to know if you can use an IF statement with multiple conditions in Calc.
Another way to do it, I think. With COUNTIF that needs to use regular expressions if the calc option for search criteria on the whole cell is enable, that is recommended because there is a bug being slow search for text with some functions like VLOOKUP.
Thanks. Not trying to count the items, but mark them as TRUE (either by changing their current cell contents or in another column). Appreciate the advice, though!
Based on your last comment to Lupp you could use the MATCH function to check each cell of your list against a list of possible options and set the value of a helper column to true.
=IFNA(MATCH(A2,$E$2:$E$4,0)>0,0)
Where $E$2:$E$4 is a list of possible matches to return true. A2 is the first cell of your list and you copy this formula down the helper column.
As you suggested you can then filter on the value of this helper column.
Hi
English is not my native language, but from what I understand, you do not need a function to get the result.
Data▸More filters▸Advanced Filter can filter a range depending of another (containing the searched strings).
Cick Options if you want to copy the result elsewhere.
In Filter.ods example, Data (A7:B35) are filtered according to the filter cell range A1:A5.
More info. here
Regards