Match data from range to different range size, use adjacent cell of smaller range of data

[rdahlke]

Hi all,

I'm trying to create a student loan calculator in Excel(google sheets) where I can add additional payments but it would only add it for a specific date not every single payment. I've used the formula: =if(day(A27)=Day($B$5),$B$6,"") where A27 is date, B5 is my scheduled monthly payment date, B6 is payment amount. This works for changing my scheduled monthly payment to a specified day of the month throughout the sheet, but what I want to do is allow 20 rows for additional payment dates,

For example B10:B30 (any date for additional payment) C10:C30 (amount of additional payment). A31:A1000 (date in 1 day increments, starts at 1/1/2016, 1/2/2016, etc) D31:D1000 (Additional payment amount). I want to enter 1/2/2016 into B10, $30 into C10 and then $30 is pops into D32. Then enter 1/5/2016 into B11, $40 into C11, then $40 pops up into D35.

Any thoughts? Any help would be greatly appreciated.

Best,

Robert

[Ochimus]

As shown in the attachment, put this formula in D30 and copy it down to D1000:

=IFERROR(INDEX(C$10:C$30,MATCH(A31,B$10:B$30,0)),"")


(Look in B10 - B30, and find the date that matches A31. If there is a match, copy the value in Col C to D31. If there is no match, Col D cell is blank.)

Does the same for each row ("$" sets the Col B dates and Col C values as "Absolute" ranges)

Ochimus

[rdahlke]

Thanks!! That worked.

[Tony Valko]

Here's another one...

=IFERROR(VLOOKUP(A31,B$10:C$30,2,0),"")

[rdahlke]

Would either of these formulas or something similar work for multiple columns? For example dates from B10:B30 and E10:E30 and H10:H30, pulling data from adjacent cells C10:C30 and F10:F30 and I10:I30 respectively to match A31:A1000 dates.

[Tony Valko]

Not sure I follow you.

Can you post a SMALL sample file and show us what results you expect.

We don't need all of this: A31:A1000 dates.

Just a few will do.

[rdahlke]

I added an sample to "manage attachments". Is this the correct place to upload the spreadsheet?

[Tony Valko]

I downloaded your file but I'm completely lost.

Tell us what to do with it.

[rdahlke]

Alright, B8:B12,D8:D12 is where I would like to add dates for additional payments. I want the information in C8:C12,E8:E12 (corresponding to B8:B12,D8:D12) to pull into respective cells G14:G19 based on dates in A14:A19.

For example, if I make an additional payment on 4/22/2016 of $10.50. I want cell G15 to be $10.50. Also if I type 5/22/2016 into D11 and $13.00 into E11, I would like cell G18 to be $13.00.

A couple of my formulas are off since I just copied a small sample of my original sheet but ultimately my goal is what was previously described.

Thanks for your help!

[Ochimus]

If you want to do that, you need to switch to "SUMiF".

As you can see in the updated attachment, I have added your two extra "blocks" of possible dates and sums into Cols E and F, and H and I.

Cell B31 now has this somewhat longer formula, copied down to B1000:

=IF(SUMIF(B$10:B$29,A31,C$10:C$29)+SUMIF(E$10:E$29,A31,F$10:F$29)+SUMIF(H$10:H$29,A31,I$10:I$29)=0,"",SUMIF(B$10:B$29,A31,C$10:C$29)+SUMIF(E$10:E$29,A31,F$10:F$29)+SUMIF(H$10:H$29,A31,I$10:I$29))

(Add up everything in C10 - C29 where the date in B10 - 29 matches A31, Then add to that subtotal everything in F10 - F29 where the date in E10 - 29 matches A31, Then add to that subtotal everything in I10 - I29 where the date in H10 - 29 matches A31. If there is no match in any of the three blocs. the cell remains empty)

As before, the "$" signs in each bloc set the range as "absolute", otherwise the range you are searching would drop one row as you moved down Col A.

You should also consider "naming" the six bloc ranges, as it makes things much easier to follow.

Ochimus