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 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.
-
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
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 63.7K Get Help
- 406 Global Discussions
- 217 Industry Talk
- 456 Announcements
- 4.7K Ideas & Feature Requests
- 141 Brandfolder
- 136 Just for fun
- 57 Community Job Board
- 459 Show & Tell
- 31 Member Spotlight
- 1 SmartStories
- 297 Events
- 37 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!