nav[aria-label="Primary Navigation"] { padding: 0; & ul { list-style: none; width: 100%; display: flex; flex-direction: row; justify-content: start; align-items: start; gap: 30px; padding: 0; & li { margin: 0; } & ul li { list-style: none; } } }

Smartsheet Community

Connect with peers, share your expertise, and inspire what’s next in Smartsheet — from proven practices to practical how-to insights from fellow users and product experts.

Can you help me with this IF formula ?

Louis.Smith

So I have two formulas that work individually but when I try to combine it won't pull in a value. The first formula is:

=IF(OR(FIND("88", [SMN_SMLR_AC_TYP_CD]@row) > 0, FIND("67", [SMN_SMLR_AC_TYP_CD]@row) > 0, FIND("73", [SMN_SMLR_AC_TYP_CD]@row) > 0, FIND("77", [SMN_SMLR_AC_TYP_CD]@row) > 0, FIND("57", [SMN_SMLR_AC_TYP_CD]@row) > 0, FIND("37", [SMN_SMLR_AC_TYP_CD]@row) > 0, FIND("73N", [SMN_SMLR_AC_TYP_CD]@row) > 0, FIND("87", [SMN_SMLR_AC_TYP_CD]@row) > 0, FIND("47", [SMN_SMLR_AC_TYP_CD]@row) > 0, FIND("17", [SMN_SMLR_AC_TYP_CD]@row) > 0, FIND("32", [SMN_SMLR_AC_TYP_CD]@row) > 0, FIND("35", [SMN_SMLR_AC_TYP_CD]@row) > 0, FIND("765", [SMN_SMLR_AC_TYP_CD]@row) > 0, FIND("220", [SMN_SMLR_AC_TYP_CD]@row) > 0, FIND("330", [SMN_SMLR_AC_TYP_CD]@row) > 0), IF(FIND("88", [SMN_SMLR_AC_TYP_CD]@row) > 0, "M88", IF(FIND("67", [SMN_SMLR_AC_TYP_CD]@row) > 0, "767", IF(FIND("73", [SMN_SMLR_AC_TYP_CD]@row) > 0, "737", IF(FIND("77", [SMN_SMLR_AC_TYP_CD]@row) > 0, "777", IF(FIND("57", [SMN_SMLR_AC_TYP_CD]@row) > 0, "757", IF(FIND("37", [SMN_SMLR_AC_TYP_CD]@row) > 0, "737", IF(FIND("73N", [SMN_SMLR_AC_TYP_CD]@row) > 0, "737", IF(FIND("87", [SMN_SMLR_AC_TYP_CD]@row) > 0, "787", IF(FIND("47", [SMN_SMLR_AC_TYP_CD]@row) > 0, "747", IF(FIND("17", [SMN_SMLR_AC_TYP_CD]@row) > 0, "717", IF(FIND("32", [SMN_SMLR_AC_TYP_CD]@row) > 0, "320", IF(FIND("35", [SMN_SMLR_AC_TYP_CD]@row) > 0, "350", IF(FIND("765", [SMN_SMLR_AC_TYP_CD]@row) > 0, "767", IF(FIND("220", [SMN_SMLR_AC_TYP_CD]@row) > 0, "220", IF(FIND("330", [SMN_SMLR_AC_TYP_CD]@row) > 0, "330", "No Match in SMN_SMLR"))))))))))))))), IF(OR(FIND("/88", [Comment 1]@row) > 0, FIND("/67", [Comment 1]@row) > 0, FIND("/73", [Comment 1]@row) > 0, FIND("/77", [Comment 1]@row) > 0, FIND("/57", [Comment 1]@row) > 0, FIND("/37", [Comment 1]@row) > 0, FIND("/73N", [Comment 1]@row) > 0, FIND("/87", [Comment 1]@row) > 0), IF(FIND("/88", [Comment 1]@row) > 0, "M88", IF(FIND("/67", [Comment 1]@row) > 0, "767", IF(FIND("/73", [Comment 1]@row) > 0, "737", IF(FIND("/77", [Comment 1]@row) > 0, "777", IF(FIND("/57", [Comment 1]@row) > 0, "757", IF(FIND("/37", [Comment 1]@row)))))))))

This is pulling correctly as all the values are in the sheet but we are wanting to add to the formula that if comment 1 and SMN_SMLR_AC_TYP_CD is blank then pull in Instructor Fleet column.

Find more posts tagged with

Accepted answers

All comments

Paul Newcome

What is the formula that isn't working?

Louis.Smith

https://community.smartsheet.com/discussion/comment/442188#Comment_442188

Sorry about that.

Paul Newcome

Your formula is actually really overbuilt. You can get rid of the of the IF/OR combos.

From there, you would drop your next series of nested IF statements into the "value if false" portion of your existing IF statement where you currently have "No Match in SMN_SMLR" (and add in the missing "IF" statement).

=IF(FIND("88", [SMN_SMLR_AC_TYP_CD]@row) > 0, "M88", IF(FIND("67", [SMN_SMLR_AC_TYP_CD]@row) > 0, "767", IF(FIND("73", [SMN_SMLR_AC_TYP_CD]@row) > 0, "737", IF(FIND("77", [SMN_SMLR_AC_TYP_CD]@row) > 0, "777", IF(FIND("57", [SMN_SMLR_AC_TYP_CD]@row) > 0, "757", IF(FIND("37", [SMN_SMLR_AC_TYP_CD]@row) > 0, "737", IF(FIND("73N", [SMN_SMLR_AC_TYP_CD]@row) > 0, "737", IF(FIND("87", [SMN_SMLR_AC_TYP_CD]@row) > 0, "787", IF(FIND("47", [SMN_SMLR_AC_TYP_CD]@row) > 0, "747", IF(FIND("17", [SMN_SMLR_AC_TYP_CD]@row) > 0, "717", IF(FIND("32", [SMN_SMLR_AC_TYP_CD]@row) > 0, "320", IF(FIND("35", [SMN_SMLR_AC_TYP_CD]@row) > 0, "350", IF(FIND("765", [SMN_SMLR_AC_TYP_CD]@row) > 0, "767", IF(FIND("220", [SMN_SMLR_AC_TYP_CD]@row) > 0, "220", IF(FIND("330", [SMN_SMLR_AC_TYP_CD]@row) > 0, "330", IF(FIND("/88", [Comment 1]@row) > 0, "M88", IF(FIND("/67", [Comment 1]@row) > 0, "767", IF(FIND("/73", [Comment 1]@row) > 0, "737", IF(FIND("/77", [Comment 1]@row) > 0, "777", IF(FIND("/57", [Comment 1]@row) > 0, "757", IF(FIND("/37", [Comment 1]@row, IF(ISBLANK(Fleet@row), [Instructor Fleet]@row, Fleet@row, "No Match in SMN_SMLR"))))))))))))))))))))

Louis.Smith

https://community.smartsheet.com/discussion/comment/442251#Comment_442251

I tried the formula provided and it gave me a Circular Reference.

Paul Newcome

That means you are trying to put the formula into a cell that is being referenced by the formula. You need to put it in a cell / column that is not being referenced by the formula.

Louis.Smith

https://community.smartsheet.com/discussion/comment/442272#Comment_442272

I see, the formula is finding data in different cells and brining into the fleet column but the end of the formula is saying that if that fleet is blank to find data from instructor fleet column. Is there a way to add something into the formula that if no data is found in the other cells then find instructor fleet

Paul Newcome

That's what you have already. The issue is because you are putting the formula into one of the columns being referenced by the formula.

Louis.Smith

https://community.smartsheet.com/discussion/comment/442297#Comment_442297

Okay I see, I think its the end of the formula.

IF(ISBLANK(Fleet@row), [Instructor Fleet]@row, Fleet@row, "No Match in SMN_SMLR"

How Could I adapt the formula to say if any of the below isn't found then instructor fleet?

The formula now is saying that if its blank but I want it to say if it doesn't find any of the values in either column then Instructor Fleet

Louis.Smith

https://community.smartsheet.com/discussion/comment/442297#Comment_442297

I tried testing this but I received incorrect argument:

I was trying to say that if any of the above formulas weren't found then insert value from instructor fleet row
=IF(FIND("88", [SMN_SMLR_AC_TYP_CD]@row) > 0, "M88", IF(FIND("67", [SMN_SMLR_AC_TYP_CD]@row) > 0, "767", IF(FIND("73", [SMN_SMLR_AC_TYP_CD]@row) > 0, "737", IF(FIND("77", [SMN_SMLR_AC_TYP_CD]@row) > 0, "777", IF(FIND("57", [SMN_SMLR_AC_TYP_CD]@row) > 0, "757", IF(FIND("37", [SMN_SMLR_AC_TYP_CD]@row) > 0, "737", IF(FIND("73N", [SMN_SMLR_AC_TYP_CD]@row) > 0, "737", IF(FIND("87", [SMN_SMLR_AC_TYP_CD]@row) > 0, "787", IF(FIND("47", [SMN_SMLR_AC_TYP_CD]@row) > 0, "747", IF(FIND("17", [SMN_SMLR_AC_TYP_CD]@row) > 0, "717", IF(FIND("32", [SMN_SMLR_AC_TYP_CD]@row) > 0, "320", IF(FIND("35", [SMN_SMLR_AC_TYP_CD]@row) > 0, "350", IF(FIND("765", [SMN_SMLR_AC_TYP_CD]@row) > 0, "767", IF(FIND("220", [SMN_SMLR_AC_TYP_CD]@row) > 0, "220", IF(FIND("330", [SMN_SMLR_AC_TYP_CD]@row) > 0, "330", IF(FIND("/88", [Comment 1]@row) > 0, "M88", IF(FIND("/67", [Comment 1]@row) > 0, "767", IF(FIND("/73", [Comment 1]@row) > 0, "737", IF(FIND("/77", [Comment 1]@row) > 0, "777", IF(FIND("/57", [Comment 1]@row) > 0, "757", IF(FIND("/37", [Comment 1]@row, IF(IFERROR(Fleet@row), [Instructor Fleet]@row)))))))))))))))))))))))

Sign in to join the conversation!

Quick Links

Component react.discussion.discussions had an error.

ref must be one of: categoryID, siteSectionID, category, category/categoryID, category/name, category/description, category/url, category/allowedDiscussionTypes, locale, siteSection, siteSection/basePath, siteSection/contentLocale, siteSection/sectionGroup, siteSection/sectionID, siteSection/name, siteSection/description, siteSection/apps, siteSection/attributes, layoutViewType, discussionID, commentID, page, latest, sort, sortType, lookupSort, discussion, discussion/name, tags, breadcrumbs, discussionApiParams, serverDraftID, serverDraft.