Multiple IF or Match

Have a scenario that I want say if column A matches Column B, then enter value column C, If Column A matches, Column D, then enter value column E, If Column A matches Column F, Then enter value column G. Is this better done through some sort of MATCH or IF. I think IF, but can't get it work work (learning as I go).

=IF([Next Payment Due]1 = [Year 1 Due]1), [Year 1]1, IF([Next Payment Due]1 = [Year 2 Due]1, [Year 2]1, IF([Next Payment Due]1 = [Year 3 Due]1, [Year 3]1, IF([Next Payment Due]1 = [Year 4 Due]1, [Year 4]1, IF([Next Payment Due]1 = [Year 5 Due]1, [Year 5]1, IF([Next Payment Due]1 =[Year 6 Due]1, [Year 6]1,0))))))

#Unparseable

Columns that are being compared are both dates, value column is text/number

Answers

  • Andrée Starå
    Andrée Starå ✭✭✭✭✭✭
    edited 07/31/20

    Hi @EMA

    Try something like this. (all columns need to be dates)


    =IF([Next Payment Due]@row = [Year 1 Due]@row; [Year 1 Due]@row; IF([Next Payment Due]@row = [Year 2 Due]@row; [Year 2 Due]@row; IF([Next Payment Due]@row = [Year 3 Due]@row; [Year 3 Due]@row; IF([Next Payment Due]@row = [Year 4 Due]@row; [Year 4 Due]@row; IF([Next Payment Due]@row = [Year 5 Due]@row; [Year 5 Due]@row; IF([Next Payment Due]@row = [Year 6 Due]@row; [Year 6 Due]@row))))))

    The same version but with the below changes for convenience.

    =IF([Next Payment Due]@row = [Year 1 Due]@row, [Year 1 Due]@row, IF([Next Payment Due]@row = [Year 2 Due]@row, [Year 2 Due]@row, IF([Next Payment Due]@row = [Year 3 Due]@row, [Year 3 Due]@row, IF([Next Payment Due]@row = [Year 4 Due]@row, [Year 4 Due]@row, IF([Next Payment Due]@row = [Year 5 Due]@row, [Year 5 Due]@row, IF([Next Payment Due]@row = [Year 6 Due]@row, [Year 6 Due]@row))))))

    Depending on your country/region, you'll need to exchange the comma to a period and the semi-colon to a comma.


    Did that work?

    I hope that helps!

    Be safe and have a fantastic weekend!

    Best,

    Andrée Starå | Workflow Consultant / CEO @ WORK BOLD

    Did my post(s) help or answer your question or solve your problem? Please help the Community by marking it as the accepted answer/helpful. It will make it easier for others to find a solution or help to answer!

    SMARTSHEET EXPERT CONSULTANT & PARTNER

    Andrée Starå | Workflow Consultant / CEO @ WORK BOLD

    W: www.workbold.com | E:andree@workbold.com | P: +46 (0) - 72 - 510 99 35

    Feel free to contact me for help with Smartsheet, integrations, general workflow advice, or anything else.

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭

    You may also be able to use something along the lines of...

    =INDEX([Year 1 Due]@row:[Year 6]@row, 1, MATCH([Next Payment Due]@row, [Year 1 Due]@row:[Year 6 Due]@row, 0) + 1)


    We use the MATCH to pull a column number for wherever it finds [Next Payment Due]@row. Then we add 1 to that column number to get the appropriate [Year #]@row. We use this column number as the column number in an INDEX function.

  • EMA
    EMA ✭✭

    Thanks. Got it. Makes sense as to how the date is returned. However, the payment amount associated with each Year are not included in the formula. this is ultimate what I want the data to populate. Paul's starts to get there (I think) but it isn't accounting for the other years.

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭

    @EMA My solution does account for the other year. It is looking across all of them as a range. Have you tried plugging it in to see if it works?

  • EMA
    EMA ✭✭

    @Paul Newcome Changed the range to have the values and YES! Learning the ins/outs here and very grateful! =INDEX([Year 1]@row:[Year 6]@row, 1, MATCH([Next Payment Due]@row, [Year 1 Due]@row:[Year 6 Due]@row, 0) + 1)

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭

    Happy to help. 👍️

    My formula already included the values. I just started the one range a single column earlier is all.

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!