MrExcel Publishing
Your One Stop for Excel Tips & Solutions

matching formula that takes two values from one table, match them to the corresponding values in a


Posted by h.omar on November 05, 2001 8:28 AM

I need a matching formula that will allow me to take two values from one table, match them to the corresponding values in another table, and return a third value.

transaction table
a, b, c (columns)

account, date, transaction
123, 10-1-01, $35.00
123, 10-5-01, $42.00
217, 10-1-01, $2.11

daily balance table
e, f, g, h, i, j (columns)

Date,account, 123, 124, 217, 218
begin bal, (blank cell), $25, $38, $0, $500
10-1-01, (blank cell), + g2, +h2, +i2, +j2
10-2-01, (blank cell), +g3, +h3, +i3, +j3
10-3-01, (blank cell), +g4, +h4, +i4, +j4
10-4-01, (blank cell), +g5, +h5, +i5, +j5
10-5-01, (blank cell), +g6, +h6, +i6, +j6

the desired result should be as follows:

daily balance table
e, f, g, h, i, j (columns)

Date,account, 123, 124, 217, 218
begin bal, (blank cell), $25, $38, $0, $500
10-1-01, (blank cell), + g2+matching formula, +h2+matching formula, +i2+matching formula, +j2+matching formula
10-2-01, (blank cell), +g3+matching formula, +h3+matching formula, +i3+matching formula, +j3+matching formula
10-3-01, (blank cell), +g4+matching formula, +h4+matching formula, +i4+matching formula, +j4+matching formula
10-4-01, (blank cell), +g5+matching formula, +h5+matching formula, +i5+matching formula, +j5+matching formula
10-5-01, (blank cell), +g6+matching formula, +h6+matching formula, +i6+matching formula, +j6+matching formula


In other words, from take the value from cell g1, find it in A2:A4. If there is a matching entry, match the date in the cell next to the matching value to the value from the original value at the matching date, and add the value to value from the preceding day.
So that for account 123 (cell g1), look in range a2:a4 and find matching value (cell a2). On 10-1-01 there was a transaction for $35.00. The formula should bring a value of $35.00 to cell g3 and add it to the value from g2.

the value in g3 should then = $60.00
the value in g7 should then = $102.00
the value in i3 should = $2.11

if there were no transactions in the transaction table to match to the daily balance table, then the matching formula should return a value of zero.
the value in h3 ,h4, h5,h6, & h7 would all be $38.00


Posted by Aladin Akyurek on November 05, 2001 10:09 AM

Hassan,

We are pretty impatient, aren't we? :)

It's done. The formula that you need is

=IF(ISNUMBER(SUMPRODUCT(MATCH(G$4&$E6,$A$5:$A$510&$B$5:$B$510,0))),INDEX($C$5:$C$510,SUMPRODUCT(MATCH(G$4&$E6,$A$5:$A$510&$B$5:$B$510,0))),0)+G5

which must be entered in G6 (I'm referring to the workbook "matching tables.xls" that you sent me) then copied across and down.

The workbook is underway to you.

PS. Please check the results thoroughly for correctness.

Aladin

=============

Posted by HASSAN OMAR on November 05, 2001 10:14 AM

As Elvis would say,

Thank you,
Thank you very much!