Using Vlookup and Contains to find the latest date

[bogusnews]

Hi everyone.
I run a window cleaning company. I have two pages on my spreadsheet, one with a list of invoices, the other with customers. They usually have regular work. So I want a column in the customer sheet to be constantly looking at the invoice list.

Everytime a new invoice is entered, I want the customer sheet to capture the date of the work. I adjusted a formula I found here to this: =VLOOKUP("*"&D4&"*",'Invoices - Main'!C$6:D500,2,FALSE)
where D4 contains the customer name. The array in on the "invoice-main" sheet and the "2" of course contains the invoice date.

This works well, but unfortunately it just chooses the first date it finds. Is there a way for it to choose the last date for the customer?

Thanks so much for your help.

 

[Yongle]

Excel 2016 (Windows) 32 bit
[Table="width:, class:head"][tr=bgcolor:#E0E0F0][th] [/th][th]

A​

[/th][th]

B​

[/th][th]

C​

[/th][/tr]
[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

1​

[/td][td].[/td][td][/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

2​

[/td][td].[/td][td][/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

3​

[/td][td]Name[/td][td]Last date[/td][td] Formula in B4[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

4​

[/td][td]Name_01[/td][td]

15/03/2019​

[/td][td] =MAXIFS('Invoices - Main'!$D$6:$D$500,'Invoices - Main'!$C$6:$C$500,A4)[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

5​

[/td][td]Name_02[/td][td]

13/03/2019​

[/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

6​

[/td][td]Name_03[/td][td]

11/03/2019​

[/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

7​

[/td][td]Name_04[/td][td]

13/03/2019​

[/td][td][/td][/tr]
[/table][Table="width:, class:grid"][tr][td]Sheet: Customers[/td][/tr][/table]


Excel 2016 (Windows) 32 bit
[Table="width:, class:head"][tr=bgcolor:#E0E0F0][th] [/th][th]

A​

[/th][th]

B​

[/th][th]

C​

[/th][th]

D​

[/th][/tr]
[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

1​

[/td][td].[/td][td][/td][td][/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

2​

[/td][td].[/td][td][/td][td][/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

3​

[/td][td].[/td][td][/td][td][/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

4​

[/td][td].[/td][td].[/td][td][/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

5​

[/td][td][/td][td][/td][td]Customer[/td][td]date[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

6​

[/td][td][/td][td][/td][td]Name_02[/td][td]

10/03/2019​

[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

7​

[/td][td][/td][td][/td][td]Name_03[/td][td]

10/03/2019​

[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

8​

[/td][td][/td][td][/td][td]Name_03[/td][td]

10/03/2019​

[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

9​

[/td][td][/td][td][/td][td]Name_04[/td][td]

10/03/2019​

[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

10​

[/td][td][/td][td][/td][td]Name_04[/td][td]

10/03/2019​

[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

11​

[/td][td][/td][td][/td][td]Name_02[/td][td]

11/03/2019​

[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

12​

[/td][td][/td][td][/td][td]Name_03[/td][td]

11/03/2019​

[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

13​

[/td][td][/td][td][/td][td]Name_03[/td][td]

11/03/2019​

[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

14​

[/td][td][/td][td][/td][td]Name_03[/td][td]

11/03/2019​

[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

15​

[/td][td][/td][td][/td][td]Name_01[/td][td]

12/03/2019​

[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

16​

[/td][td][/td][td][/td][td]Name_01[/td][td]

13/03/2019​

[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

17​

[/td][td][/td][td][/td][td]Name_01[/td][td]

13/03/2019​

[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

18​

[/td][td][/td][td][/td][td]Name_02[/td][td]

13/03/2019​

[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

19​

[/td][td][/td][td][/td][td]Name_02[/td][td]

13/03/2019​

[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

20​

[/td][td][/td][td][/td][td]Name_02[/td][td]

13/03/2019​

[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

21​

[/td][td][/td][td][/td][td]Name_04[/td][td]

13/03/2019​

[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

22​

[/td][td][/td][td][/td][td]Name_04[/td][td]

13/03/2019​

[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

23​

[/td][td][/td][td][/td][td]Name_04[/td][td]

13/03/2019​

[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

24​

[/td][td][/td][td][/td][td]Name_01[/td][td]

14/03/2019​

[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

25​

[/td][td][/td][td][/td][td]Name_01[/td][td]

14/03/2019​

[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

26​

[/td][td][/td][td][/td][td]Name_01[/td][td]

15/03/2019​

[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

27​

[/td][td].[/td][td][/td][td][/td][td][/td][/tr]
[/table][Table="width:, class:grid"][tr][td]Sheet: Invoices - Main[/td][/tr][/table]

 

[bogusnews]

Ahhh.... thank you so much for the reply. I'll try that now....

 

[bogusnews]

Thank you. I tried this. It seems to be giving the answer of 0/01/1900.

Some more information... I've used the MS invoice tracker template. It has hard wired in it to connect the customer number along with the customer name in the invoice - main page. So a client called "Service King" is treated as "60 - Service King". This may be causing me problems perhaps...?

Thank you again for your prompt response

 

[bogusnews]

Ahh... got it. added "*"&A4. Seems to work now ))) Great!

 

[Yongle]

thanks for your feedback