Multiple IF statements/Managing large formula.


Hello Smartsheets Community,

I was curious if anyone had some input. I've developed a sheet that runs different formulas for different "vendors" in a column to get a final price based on the vendors data. It's become a very large IF statement as there's many vendors now (if vendor is Vendor 1, *do this* etc). I'd like to make an easier way to add vendors into this formula as we get new ones throughout the year. Is it at all possible to say, have a sheet that I could enter the vendors and data into, and "match" a formula onto another sheet instead of using a long formula? For example, instead of writing a formula for each vendor in one large formula, be able to call on a different formula for each vendor in another way? Hopefully that makes sense, looking to make it easier for other team members to add vendors into the formula. May or may not be possible.

Thanks for any thoughts!



  • Jeff Reisman
    Jeff Reisman ✭✭✭✭✭✭
    edited 02/15/23

    @Matt Desjardin

    This is mostly possible, with some tweaks. Much of it depends on what your formulas are doing, where they are getting data values from, etc.

    Say Vendor A has a price multiplier of .71, and Vendor B has a multiplier of .75. A lookup sheet listing this data in Vendor and Multiplier columns could be useful in calculating prices on an order sheet:

    Vendor SKU Qty Price Total

    Vendor A Widget001 2 $8715.00

    The Total column could use an INDEX/MATCH to bring in the multiplier value and apply it to Qty x Price:

    =Qty@row * Price@row * INDEX({Lookup Sheet Multiplier Column Range}, MATCH(Vendor@row, {Lookup Sheet Vendor Column Range}, 0))

    The result is a total of $12,375.30

    If you could share some screenshots, specifics of what your formulas need to do, I could help you with the logic and syntax.


    Jeff Reisman

    Link: Smartsheet Functions Help Pages Link: Smartsheet Formula Error Messages

    If my answer helped solve your issue, please mark it as accepted so that other users can find it later. Thanks!

  • Matt Desjardin

    @Jeff Reisman

    Thank you for your detailed reply I appreciate it. Going to work with this formula and see if I can tweak it for what I need. Some vendors require slightly different formulas, so if I can't get it to work I'll forward some images of my project! Thanks again for your time.



Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!