Multiple IF or Match

EMA

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

Andrée Starå

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!

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

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

@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

@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

Happy to help. 👍️

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