I have an amortization table and I want to be able to calculate extra payments on the loan by entering the extra payment amount in a cell as well as the payment numbers and have the table return those values in the extra payment column only if they correspond to the specified payment number in the column to the left. (Ex. **see photo** L6=$1,000, L7=3,5:7,10,15... Table Column [ExtraPayments] should have $1,000 in the rows which have 3,5:7,10,15 in the [PMT NO] column on the left ([PMT NO] is not the same as the rows since the table starts in row 12) in the photo you see my current formula has it showing in every cell, I need the $1,000 to only show in the specified locations). Does this make sense? I don't really know how to explain it.
@Twifoo's solution is elegant and compact but relies upon hard-coding both the extra payment periods and amount within the formula. To avoid this some way of parsing or searching the comma-separated string is needed. I chose the latter, first padding the list with additional commas to give a name ONE.TIME that refers to
= comma & ONE.TIME.LIST & comma
where 'comma' refers to
(just to improve legibility).
The test to determine whether the payment number has an associated one-time payment is