Hello
I am having issues making the following formula pull in the most recent payment amount from another tab.
=IF(H7=W7,0,IF(J7<=$A$1,IFERROR(LOOKUP(9.99999999999999E+307,SEARCH($A7,Payment!A:A),Payment!P:P),0),0))
What I am trying to do in column V is if the amount in column H = the amount in column W then 0. If this is not the case then to look at the date in column J and if it is less than or equal to the date in cell A2 the compare column A on the data tab to column A on the payment tab.
This is where I am having the issue, if there are two payments to bring the mot current payment amount back.
For example if there is a payment made on 05/18/18 for $10,000 and another payment made on 06/19/18 for $15,000 I would like it to bring back the $15,000 amount.
On the data tab Cell A2 = 06/19/2018 the date in cell J7 = 06/15/2018 so in this case it matches the criteria but it is bringing back the first payment it finds.
Any thoughts on how to correct this would be greatly appreciated
Thank you
Mark
I am having issues making the following formula pull in the most recent payment amount from another tab.
=IF(H7=W7,0,IF(J7<=$A$1,IFERROR(LOOKUP(9.99999999999999E+307,SEARCH($A7,Payment!A:A),Payment!P:P),0),0))
What I am trying to do in column V is if the amount in column H = the amount in column W then 0. If this is not the case then to look at the date in column J and if it is less than or equal to the date in cell A2 the compare column A on the data tab to column A on the payment tab.
This is where I am having the issue, if there are two payments to bring the mot current payment amount back.
For example if there is a payment made on 05/18/18 for $10,000 and another payment made on 06/19/18 for $15,000 I would like it to bring back the $15,000 amount.
On the data tab Cell A2 = 06/19/2018 the date in cell J7 = 06/15/2018 so in this case it matches the criteria but it is bringing back the first payment it finds.
Any thoughts on how to correct this would be greatly appreciated
Thank you
Mark
