Find the latest date based on a condition from a range of data that is unlimited

Hello Everyone,

I am trying to find the latest date based on a condition from a range of data that is unlimited. i can do it if the range is limited and defined but i am having trouble trying to figure out what to do if the range is unlimited. i can't use Maxifs because i have excel 2016. Here is what i have...

I have a register that is auto-generated from an invoice using vba. It contains a date column and a customerid column.

First Sheet: Register
InvoiceDate CustomerID
17-Feb-21 1
18-Feb-21 2
18-Feb-21 1
12-Jan-20 3
12-Dec-20 4
14-Mar-20 1
17-Jul-20 2
19-Feb-20 1
19-Feb-20 2
Both the date and customerid columns are autogenerated daily

I made a second sheet called "Latest". In this sheet, i want to know the latest date that an invoice was generated for a customer. So when i enter customerid no. 1 in the first column, the second column should automatically give me the latest invoice generated for this customer.

Sheet:Latest
CustomerID Latest Invoice Date
1 ?

The user can enter any CustomerID in the first column
The second column should automatically show the latest date from the Date column in the Register sheet

I would be grateful for any advise.
Thank you in advance.

Hi Glenn
Thank you so much for your quick reply. i tried attaching a sample file but apparently i dont have 'permission' to attach files. Anyway thank you so much for the solution...it works perfectly in my actual file. I was trying with max and index and vlookup and i couldn't get it. The one you gave is perfect. Thank you again. Bless you

hi, it was a sample file. small one. the message i got was...

you do not have permission to access this page. This could be due to one of several reasons:
1. Your user account may not have sufficient privileges to access this page. Are you trying to edit someone else's post, access administrative features or some other privileged system?
2. If you are trying to post, the administrator may have disabled your account, or it may be awaiting activation.

anyway i sent a message to the forum/site administrator...i am waiting for their reply.