Using a look-up formula to find the first date among a variaty of dates associated with ID

[Javersoon]

Hallo everyone.

Let me try to explain my problem:

In an ERP-system I have a lot of ID's under which there is a lot of different payments. So fx ID=100000100 can contain 10 payments each with a different reciept date:

ID Amount Recipt date
100000100 €100 20130101
100000100 €-100 20130607
100000100 € 500 20141121
100000100 and so on.......................................
100000100
100000100

I need a formula that can lookup the very first reciept date for each ID (not the second, third og fouth date, but the very first reciept date).
I already know which ID's I wanna look up - I have that information in a speadsheet.

I hope you can be of help!

Kind regards

[Bernie Deitrick]

I have assumed by first date, your mean the earliest date, not the date that is the highest up in your list of data.

If your dates are in the form of a number - YYYYMMDD - or are actual dates formatted in any way you want, and your data is as shown in Columns A:C, then with the first ID of interest in Cell E2, you can use an array formula like this, entered using Ctrl-Shift-Enter)

=MIN(IF($A$2:$A$1000=E2,IF($C$2:$C$1000>0,$C$2:$C$1000)))


You could also use a pivot table to find all the earliest dates....

[Javersoon]

Hallo Bernie

Thanks for the answer

I Think I need just a litte more help.

In one spreadsheet (spreadsheet1) I have the following informations, which are the informations I wanna lookup:

ColumnA=ID ColumnB=amount ColumnC=reciptdate
100085645914 6,53 20180920
100067194259 30746,3 20170506
100081397757 73,75 20180423

I wanna check, wheather or not the recipt date in spreadsheet1 for each ID corrospond with the earliest date for each ID in the ERP-system (spreadsheet2), where the same ID are represented many times:

ColumnA=ID ColumnB=Recipt ColumnC=amount
100067194259 20170405 30.746,30
100067194259 20170506 -30.746,30
100067194259 20170506 30.746,30
100085645914 20180920 -6,53
100085645914 20180920 6,53
100085645914 20180724 6,53
100081397757 20180423 73,75


Can you talk me through the formula (I am here to learn), on how you would make a formula that could check weather or not the recipt in speadsheet1 actually is the earliest date for each ID.

Kind regards, and thanks for your patients!

[Bernie Deitrick]

I took you use of the term 'spreadsheet' to mean a sheet within a workbook - so, with two sheets in the same workbook, here is an example array formula, entered using Ctrl-Shift-Enter: Since you are using commas and decimals, you may also need to change the commas in my formulas to semicolons. I hope that Excel handles it automatically when you open the attachment.

[Javersoon]

Yeah, sorry about that. Communication can be a *****.

But thanks! It makes perfect sense. And nice use of the MIN-formula by the way.

Great learning!