IF(FIND Function with OR

BTMarketingOps · 04/30/19

I am trying to build a formula that searches in a column for a certain word using the IF(FIND formula but I would like to add in an OR to that. So I would like to use this function: =IF(FIND("Design", [Project Description]4) > 0, [Estimated Hours:]4, "") which works great, but add in other words such as Graphics to it. So the formula would search for the word design or the word graphics and if it finds it, then return the data from the Estimated Hours column. I cannot seem to get it right so including the formula that I know does work, but asking how I would build an OR to this formula.

Thank you!

Paul Newcome · 05/01/19

Give this one a whirl...

=IF(OR(FIND("Design", [Project Description]4) > 0, FIND("Graphics", [Project Description]4) > 0), [Estimated Hours:]4, "")

BTMarketingOps · 05/01/19

Yes! Thank you! I tried something like that but didn't have it just right. You just saved me hours of work. Much appreciated!

Schiff A. · 05/01/19

This is the best technique to use here. One modification I'd make is to use @row instead of the row number to make the formula more resilient.

For those unfamiliar with @row, it replaces a row number in a cell reference and tells the function to look at the same row as that formula.

In this case, the IF statement is looking at other columns in the same row, so @row would be good to use here. While not strictly speaking necessary, it is a way to make your formulas more resistant to breaking if the formula is copied out of the cell editor as opposed to copying the entire cell.

More information about @row: https://help.smartsheet.com/articles/2476491-create-efficient-formulas-with-at-cell

Paul Newcome · 05/02/19

Happy to help!

Also please note... The below post in regards to @row may be helpful as well. I very frequently use @row unless I am specifically referencing a different row.

I did not use it in my solution because I was just making the more immediate modifications, but again... @row is a very helpful tool.

Paul Newcome · 05/02/19

Thanks for this! I generally use @row myself. I was just using a little copy/paste in my above solution and didn't think to throw it in there.

IF(FIND Function with OR

Comments

Help Article Resources

Categories