The Oldest Date in a DATA Dump based on Vendor number

[mrwjca]

Hello,

It has been a while that I popped by here but everything worked for me without issue’s until today.
I have a challenge and I just can’t find the solution right now.

Following problem:
I have a workbook with much data, it is an invoices dump from SAP, I will spare you the details.
Let me see if I can explain what I need.

I have my “DATA” sheet and I have “CONTACT” sheet.
In my “CONTACT” sheet I need to get the DATE of an invoice but it has to be the oldest date. This information has to come from the “DATA” sheet.

In the “DATA” sheet I have my Vendor Column (A) and the Date Column (P)
The date has to show up in the CONTACT sheet Date Column (G) (in this sheet there is also a vendor column (B))

Now I have used:
=SMALL(VLOOKUP(B22;Data!A:P;16;0);1)
And
=MIN(VLOOKUP(B22;Data!A:P;16;0);1)

For the SMALL function it resulted in an old date but the incorrect one, it took10-3-2018 instead 23-6-2017:

Sample info:
23-6-2017
10-3-2018
24-12-2018
28-12-2018
10-3-2019

For the MIN function it resulted in 1-1-1900

So the SMALL Function would be the best but somehow the result is incorrect (All cells are format Short Date)

Where am I going wrong?

Please note I am a bit limited in resources, I am at work and the resources are limited.
Thank you in advance for your help,

Willem

 

[AlanY]

both functions should give you the correct answer.
the problem is vlookup will only produce a single answer instead of an array that required.

Book1
	A	B	C
1	23/06/2017		23/06/2017
2	10/03/2018		23/06/2017
3	24/12/2018
4	28/12/2018
5	10/03/2019
Sheet5

Cell Formulas
Range	Formula
C1	=SMALL(A1:A5,1)
C2	=MIN(A1:A5)

can try something like this

Book1
	A	B	C	D	E
1	23/06/2017	V1		V1	23/06/2017
2	10/03/2018	V2		V2	10/03/2018
3	24/12/2018	V2
4	28/12/2018	V1
5	10/03/2019	V1
Sheet5

Cell Formulas
Range	Formula
E1	{=SMALL(IF($B$1:$B$5=D1,$A$1:$A$5),1)}
E2	{=SMALL(IF($B$1:$B$5=D2,$A$1:$A$5),1)}
Press CTRL+SHIFT+ENTER to enter array formulas.

 

[mrwjca]

Hi,

Thx for your reply.
Not sure what happened.
But I opened my excel sheet today, vlookup formula still in place (saved it last night).
The result this morning is that I am getting the correct dates.
Need to do some more testing an see if I can find out where it went wrong.