2 index and 2 match help

[trekiehb]

I would like to index and match two separate columns previous invoices and current invoices and if match bring in the amount of invoice.

Current invoice date amount receiving date amount previous invoces date amount
1210 01-Jan 10 1210 06-Jan #N/A 10 1211 01-Dec 15
1212 02-Jan 20 1213 06-Jan #N/A #N/A 1213 02-Dec 25
1214 03-Jan 30 1215 06-Jan #N/A #N/A 1215 03-Dec 35
1216 04-Jan 40 1218 06-Jan #N/A 50 1217 04-Dec 45
1218 05-Jan 50 1217 06-Jan #N/A #N/A 1219 05-Dec 55

=INDEX(curamt,MATCH(E2,curinv,0))*INDEX(prvamt,MATCH(E2,prvinv,0))
=INDEX(curamt,MATCH(E2,curinv,0))

I don't know if i am even using the right formula to get this to work? The first formula does not work i get a #n/a The second formula works, but it does not search the previous invoices. So please help with what am i doing wrong

Thank you
Helen

 

[T. Valko]

I would like to index and match two separate columns previous invoices and current invoices and if match bring in the amount of invoice.

Current invoice date amount receiving date amount previous invoces date amount
1210 01-Jan 10 1210 06-Jan #N/A 10 1211 01-Dec 15
1212 02-Jan 20 1213 06-Jan #N/A #N/A 1213 02-Dec 25
1214 03-Jan 30 1215 06-Jan #N/A #N/A 1215 03-Dec 35
1216 04-Jan 40 1218 06-Jan #N/A 50 1217 04-Dec 45
1218 05-Jan 50 1217 06-Jan #N/A #N/A 1219 05-Dec 55

=INDEX(curamt,MATCH(E2,curinv,0))*INDEX(prvamt,MATCH(E2,prvinv,0))
=INDEX(curamt,MATCH(E2,curinv,0))

I don't know if i am even using the right formula to get this to work? The first formula does not work i get a #n/a The second formula works, but it does not search the previous invoices. So please help with what am i doing wrong

Thank you
Helen

I can't tell what's what in your posted sample data. It all runs together!

You're trying to compare what to what? The posted column headers don't really show a good delimited table.

 

[trekiehb]

I tried the HTML program but could not figure out how to use? So I will explain best i can.

The first three columns(A,B,C) are a list of current invoices with dates and amounts.

The next columns (E,F) are the receiving invoice and date.

(G) is where i tried this formula with #n/a
=INDEX(curamt,MATCH(E2,curinv,0))*INDEX(prvamt,MATCH(E2,prvinv,0))

(H) is this formula that works for one side matching (so i could see if I did INDEX, MATCH right)

The last three columns (J,K,L) are a list of previous invoices with date and amounts.

Hope this makes sense

 

[T. Valko]

I tried the HTML program but could not figure out how to use? So I will explain best i can.

The first three columns(A,B,C) are a list of current invoices with dates and amounts.

The next columns (E,F) are the receiving invoice and date.

(G) is where i tried this formula with #n/a
=INDEX(curamt,MATCH(E2,curinv,0))*INDEX(prvamt,MATCH(E2,prvinv,0))

(H) is this formula that works for one side matching (so i could see if I did INDEX, MATCH right)

The last three columns (J,K,L) are a list of previous invoices with date and amounts.

Hope this makes sense

It would be better if you could post a sample file then we'd be able see the exact layout.

You can use a free file hosting site if need be. There's lots of them to choose from.

If you can do that then I'll take a look at the file.

 

[shift-del]

The first three columns(A,B,C) are a list of current invoices with dates and amounts.

The next columns (E,F) are the receiving invoice and date.

(G) is where i tried this formula with #n/a
=INDEX(curamt,MATCH(E2,curinv,0))*INDEX(prvamt,MATCH(E2,prvinv,0))

(H) is this formula that works for one side matching (so i could see if I did INDEX, MATCH right)

The last three columns (J,K,L) are a list of previous invoices with date and amounts.

I hope my crystal ball got it right.

<table valign="middle" colspan="12" style="font-family:Calibri; color:#000000; background-color:#FFFFFF; font-size:11px; font-weight:bold; font-style:normal; " border="1" cellpadding="2" cellspacing="0"><colgroup><col width="28pt"><col width="43,5pt"><col width="64,5pt"><col width="64,5pt"><col width="60pt"><col width="39,75pt"><col width="60,75pt"><col width="57,75pt"><col width="60pt"><col width="46,5pt"><col width="67,5pt"><col width="67,5pt"></colgroup><tbody><tr style="background-color:#FAFAFA"><td colspan="12" align="center">Worksheet 'Tabelle1'</td></tr><tr style="background-color:#cacaca"><td>
</td><td align="center">A</td><td align="center">B</td><td align="center">C</td><td align="center">D</td><td align="center">E</td><td align="center">F</td><td align="center">G</td><td align="center">H</td><td align="center">I</td><td align="center">J</td><td align="center">K</td></tr><tr><td style="background-color:#cacaca" align="center">1</td><td align="left">curr.inv.</td><td align="left">curr.inv.date</td><td align="left">curr.inv.amt.</td><td align="right">
</td><td align="left">rec.inv.</td><td align="left">rec.inv.date</td><td align="left">rec.inv.amt</td><td align="right">
</td><td align="left">prev.inv.</td><td align="left">prev.inv.date</td><td align="left">prev.inv.amt.</td></tr><tr><td style="background-color:#cacaca" align="center">2</td><td style="font-weight:normal; " align="right">1210</td><td style="font-weight:normal; " align="right">01.01.2011</td><td style="font-weight:normal; " align="right">10</td><td style="font-weight:normal; " align="right">
</td><td style="font-weight:normal; " align="right">1210</td><td style="font-weight:normal; " align="right">06.01.2011</td><td style="font-weight:normal; " align="right">10</td><td style="font-weight:normal; " align="right">
</td><td style="font-weight:normal; " align="right">1211</td><td style="font-weight:normal; " align="right">01.12.2010</td><td style="font-weight:normal; " align="right">15</td></tr><tr><td style="background-color:#cacaca" align="center">3</td><td style="font-weight:normal; " align="right">1212</td><td style="font-weight:normal; " align="right">02.01.2011</td><td style="font-weight:normal; " align="right">20</td><td style="font-weight:normal; " align="right">
</td><td style="font-weight:normal; " align="right">1213</td><td style="font-weight:normal; " align="right">06.01.2011</td><td style="font-weight:normal; " align="right">25</td><td style="font-weight:normal; " align="right">
</td><td style="font-weight:normal; " align="right">1213</td><td style="font-weight:normal; " align="right">02.12.2010</td><td style="font-weight:normal; " align="right">25</td></tr><tr><td style="background-color:#cacaca" align="center">4</td><td style="font-weight:normal; " align="right">1214</td><td style="font-weight:normal; " align="right">03.01.2011</td><td style="font-weight:normal; " align="right">30</td><td style="font-weight:normal; " align="right">
</td><td style="font-weight:normal; " align="right">1215</td><td style="font-weight:normal; " align="right">06.01.2011</td><td style="font-weight:normal; " align="right">35</td><td style="font-weight:normal; " align="right">
</td><td style="font-weight:normal; " align="right">1215</td><td style="font-weight:normal; " align="right">03.12.2010</td><td style="font-weight:normal; " align="right">35</td></tr><tr><td style="background-color:#cacaca" align="center">5</td><td style="font-weight:normal; " align="right">1216</td><td style="font-weight:normal; " align="right">04.01.2011</td><td style="font-weight:normal; " align="right">40</td><td style="font-weight:normal; " align="right">
</td><td style="font-weight:normal; " align="right">1218</td><td style="font-weight:normal; " align="right">06.01.2011</td><td style="font-weight:normal; " align="right">50</td><td style="font-weight:normal; " align="right">
</td><td style="font-weight:normal; " align="right">1217</td><td style="font-weight:normal; " align="right">04.12.2010</td><td style="font-weight:normal; " align="right">45</td></tr><tr><td style="background-color:#cacaca" align="center">6</td><td style="font-weight:normal; " align="right">1218</td><td style="font-weight:normal; " align="right">05.01.2011</td><td style="font-weight:normal; " align="right">50</td><td style="font-weight:normal; " align="right">
</td><td style="font-weight:normal; " align="right">1217</td><td style="font-weight:normal; " align="right">06.01.2011</td><td style="font-weight:normal; " align="right">45</td><td style="font-weight:normal; " align="right">
</td><td style="font-weight:normal; " align="right">1219</td><td style="font-weight:normal; " align="right">05.12.2010</td><td style="font-weight:normal; " align="right">55</td></tr></tbody></table>
<table valign="middle" colspan="2" style="color:#000000; background-color:#FFFFFF; font-family:Calibri; font-size:10px;" border="3" cellpadding="2" cellspacing="0"><tbody><tr style="background-color:#eeaaee"><td>Name</td><td>Range</td></tr><tr><td>curamt</td><td>=Tabelle1!$C$2:$C$10</td></tr><tr><td>curinv</td><td>=Tabelle1!$A$2:$A$10</td></tr><tr><td>prvamt</td><td>=Tabelle1!$K$2:$K$10</td></tr><tr><td>prvinv</td><td>=Tabelle1!$I$2:$I$10</td></tr></tbody></table>
<table valign="middle" colspan="2" style="table-layout:auto; color:#000000; background-color:#FFFFFF; font-family:Calibri; font-size:10px;" border="3" cellpadding="2" cellspacing="0"><colgroup><col width="40pt"><col></colgroup><tbody><tr style="background-color:#eeaaaa"><td>Cell</td><td>Formula</td></tr><tr><td>G2</td><td>=IFERROR(INDEX(curamt,MATCH(E2,curinv,0)),0)+IFERROR(INDEX(prvamt,MATCH(E2,prvinv,0)),0)</td></tr></tbody></table><table style="font-family:Arial; font-size:7pt"><tbody><tr><td style="color:#333333">Created with Tab2HTML (v2.4.1). ©Gerd alias Bamberg</td></tr></tbody></table>

 

[trekiehb]

Thank you, thank you, i love your crystal ball

 

[shift-del]

Thank you, thank you, i love your crystal ball

I'll pass it on.