How to retrieve two dates for one invoice number

danhendo888

Board Regular
Joined
Jul 15, 2019
Messages
142
Office Version
  1. 365
Platform
  1. Windows
Invoice NumberPayment Date
10017/01/2019
10017/25/2019

<tbody>
</tbody>

Hello,

I have instances where a customer will pay one invoice split into multiple payments.

E.g.
Half of invoice 1001 was paid on 1 July.
Other half was paid on 25 July.

In this example, I want to be able to search for invoice 1001 and have a formula that shows me the payment dates corresponding to that invoice number.

How do I go about this?

Thank you
 

Excel Facts

Quick Sum
Select a range of cells. The total appears in bottom right of Excel screen. Right-click total to add Max, Min, Count, Average.
this is one way


Book1
ABCDEFGH
1Invoice NumberPayment DateInvoice NumberPayment Date
2100101/07/2019100101/07/201925/07/201908/08/201922/08/2019
3100125/07/2019100201/08/201929/08/201905/09/201912/09/2019
4100201/08/2019100315/08/2019
5100108/08/2019
6100315/08/2019
7100122/08/2019
8100229/08/2019
9100205/09/2019
10100212/09/2019
Sheet1
Cell Formulas
RangeFormula
E2{=IFERROR(SMALL(IF($A$2:$A$10=$D2,$B$2:$B$10),COLUMN(E$1)-COLUMN($D$1)),"")}
Press CTRL+SHIFT+ENTER to enter array formulas.
 
Upvote 0
this is one way

ABCD
EFGH
1Invoice NumberPayment DateInvoice NumberPayment Date
2100101/07/2019100101/07/201925/07/201908/08/201922/08/2019
3100125/07/2019100201/08/201929/08/201905/09/201912/09/2019
4100201/08/2019100315/08/2019
5100108/08/2019
6100315/08/2019
7100122/08/2019
8100229/08/2019
9100205/09/2019
10100212/09/2019

<colgroup><col style="width: 25pxpx"><col><col><col><col><col><col><col><col></colgroup><thead>
</thead><tbody>
</tbody>
Sheet1

Array Formulas
CellFormula
E2{=IFERROR(SMALL(IF($A$2:$A$10=$D2,$B$2:$B$10),COLUMN(E$1)-COLUMN($D$1)),"")}

<thead>
</thead><tbody>
</tbody>
Entered with Ctrl+Shift+Enter. If entered correctly, Excel will surround with curly braces {}.
Note: Do not try and enter the {} manually yourself

<tbody>
</tbody>

Thank you very much, I appreciate it.

Question - how did you paste the excel table into your post?
 
Upvote 0
this is one way

ABCDEFGH
1Invoice NumberPayment DateInvoice NumberPayment Date
2100101/07/2019100101/07/201925/07/201908/08/201922/08/2019
3100125/07/2019100201/08/201929/08/201905/09/201912/09/2019
4100201/08/2019100315/08/2019
5100108/08/2019
6100315/08/2019
7100122/08/2019
8100229/08/2019
9100205/09/2019
10100212/09/2019

<colgroup><col style="width: 25pxpx"><col><col><col><col><col><col><col><col></colgroup><thead>
</thead><tbody>
</tbody>
Sheet1

Array Formulas
CellFormula
E2{=IFERROR(SMALL(IF($A$2:$A$10=$D2,$B$2:$B$10),COLUMN(E$1)-COLUMN($D$1)),"")}

<thead>
</thead><tbody>
</tbody>
Entered with Ctrl+Shift+Enter. If entered correctly, Excel will surround with curly braces {}.
Note: Do not try and enter the {} manually yourself

<tbody>
</tbody>

For the formula in E2:
Why doesn't adding
"*"&​
work? As in:

{=IFERROR(
SMALL(IF($A$2:$A$10="*"&$D2,$B$2:$B$10),COLUMN(E$1)-COLUMN($D$1)),""
)}


For example, suppose the invoice list was:
M1000
M1001
M1002

And my search criteria was 1001

Is there a way to incorporate *?
 
Upvote 0
You could try this version, copied across and down.


Excel 2016
ABCDEFGHI
1Invoice NumberPayment DateInvoice NumberPayment Date
2ABC10011/07/201910011/07/201925/07/20198/08/201922/08/2019
3ABC100125/07/201910021/08/201929/08/20195/09/201912/09/2019
4M10021/08/2019100315/08/2019
5ABC10018/08/2019
6XX100315/08/2019
7ABC100122/08/2019
8M100229/08/2019
9M10025/09/2019
10M100212/09/2019
11
Invoice Dates
Cell Formulas
RangeFormula
E2=IFERROR(INDEX($B$2:$B$10,AGGREGATE(15,6,(ROW($B$2:$B$10)-ROW($B$2)+1)/(RIGHT($A$2:$A$10,LEN($D2))=$D2&""),COLUMNS($E:E))),"")
 
Upvote 0
You could try this version, copied across and down.

Excel 2016
ABCDEFGHI
1Invoice NumberPayment DateInvoice NumberPayment Date
2ABC10011/07/201910011/07/201925/07/20198/08/201922/08/2019
3ABC100125/07/201910021/08/201929/08/20195/09/201912/09/2019
4M10021/08/2019100315/08/2019
5ABC10018/08/2019
6XX100315/08/2019
7ABC100122/08/2019
8M100229/08/2019
9M10025/09/2019
10M100212/09/2019
11

<colgroup><col style="width: 25pxpx"><col><col><col><col><col><col><col><col><col></colgroup><thead>
</thead><tbody>
</tbody>
Invoice Dates

Worksheet Formulas
CellFormula
E2=IFERROR(INDEX($B$2:$B$10,AGGREGATE(15,6,(ROW($B$2:$B$10)-ROW($B$2)+1)/(RIGHT($A$2:$A$10,LEN($D2))=$D2&""),COLUMNS($E:E))),"")

<thead>
</thead><tbody>
</tbody>

<tbody>
</tbody>
That works beautifully.
Thank you, Peter_SSs, I appreciate it :D
 
Upvote 0

Forum statistics

Threads
1,213,543
Messages
6,114,236
Members
448,555
Latest member
RobertJones1986

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top