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

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 semicolon 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.

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.

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.

@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?

@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)

Happy to help. 👍️
My formula already included the values. I just started the one range a single column earlier is all.
Help Article Resources
Categories
Check out the Formula Handbook template!