• 414K Members
• 7,697 Online
• 478K Conversations

## Return value only in rows specified by entering a number in an exterior cell to the table

Occasional Visitor

# Return value only in rows specified by entering a number in an exterior cell to the table

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.

2 Replies

# Re: Return value only in rows specified by entering a number in an exterior cell to the table

You may enter this formula in F12, copied down the rows:
=SUMPRODUCT(({3,5,6,7,10,15}=B12)*1000)

# Re: Return value only in rows specified by entering a number in an exterior cell to the table

@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

= ISNUMBER( SEARCH( comma & amortization[@[PMT NO.]] & comma, ONE.TIME) )

which is held as a named formula 'ONE.TIME.NO?'.

Finally to populate the 'EXTRA PAYMENT' column (your capitals, I am not shouting at you :-)

= IF( ONE.TIME.NO?, ONE.TIME.AMOUNT, "" )

Another catch is that the user may embed spaces in the list of one-time payments, in which case an extra processing step is needed to replace them with null strings.

Related Conversations
Stable version of Edge insider browser
HotCakeX in Discussions on
35 Replies
flashing a white screen while open new tab
cntvertex in Discussions on
14 Replies
Tabs and Dark Mode
cjc2112 in Discussions on
23 Replies
How to Prevent Teams from Auto-Launch
chenrylee in Microsoft Teams on
28 Replies