How can I use two conditions to return a specific result in another column

I am working on creating a form for stakeholders to complete that will populate a smartsheet that we've had in place for several months. This would create efficiencies for us and help our stakeholders as well. I want to be able to do the following:

When a specific selection of department is made, and the spend amount is above or below a specific number, I want to automatically populate who is being assigned to that record. There are multiple departments that can be assigned to each person, and the spend amount would be the true differentiation.

For Example:

Sally and Mary handle smaller spend under $500K
- Sally supports Marketing, Finance, and IT
- Mary supports HR, Facilities, and Maintenance
Joe and Karen handle larger spend over $500K
- Joe supports Marketing and HR
- Karen supports IT, Facilities and Maintenance

A) How would I want to write the formula for the column that would define who the record is assigned to?

B) How would I display this in the form automatically once those form fields are populated with data?

Find more posts tagged with

Forms

Formulas

Support

Solution Center

Accepted answers

All comments

Werner Gerstacker

For A) try somthing like this:

=IF(Amount<500,IF(OR(Department="Marketing", Department="Finance", Department="IT"), "Sally","Mary"), IF(OR(Department="Marketing",Department="HR"), "Joe", "Karen"))

This is the shortest version and does not check, if you have e.g. a record for Finance with more than $500k has been entered - which is not one of your options, so it would default to Karen based on the IFs used.

For B), i.e. to display this in the form you would probably have to work with a whole bunch of helper columns that will limit the selections in dropdown boxes or for radio buttons and then aggregate them back to a single field. I did this once, but it's very messy - and almost broke my brains 🤯

It might be better (definitely: much easier) just to add a description field in the form that states the algorithm exactly as you wrote it above and let the IF-functions in the column do the thing in the background.

D Gray

The first half of that formula worked beautifully, however, when I added the second half it became unparceable. It also would get very long given the configurations I need to identify.

I think I may have found another option thats a bit cleaner using an IF VLOOKUP option. I created a separate table that has 3 columns: Department, Under250000, and Over250000. Again, this one works for the first half of the formula, but for some reason I am still getting an unparceable result when I add in the second half. What am I doing wrong?

This works

=IF([Estimated Contract Value]1 < 250000, VLOOKUP(Department1, {Department/Assigned To List Range 2}, 2, false))

but when I add in the rest it becomes unparceable

=IF([Estimated Contract Value]1 < 250000, VLOOKUP(Department1, {Department/Assigned To List Range 2}, 2, false)),OR([Estimated Contract Value]1 > 250000, VLOOKUP(Department1, {Department/Assigned To List Range 2}, 3, false))

Werner Gerstacker

The easiest solution is probably to create two different lookup tables - one for below and one for above 250000 - and refer to each them separately from the value_if_true and the value_if_false section of the function based on <250000 is true or false.

VLOOKUP itself does not allow to use multiple conditions, e.g. above 250000 and IT, so you would have to use an INDEX/MATCH combination to achieve this if you want to keep it all in one table.

I've done this before but I'm not an expert on it - but you will probably be able to find some great examples on this site for it.

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, sort, discussion, discussion/name, tags, breadcrumbs, discussionApiParams, serverDraftID, serverDraft.