Pull Most Recent Invoice Data for An Account Using INDEX/MATCH/VLOOKUP/MAX (Or Other)

John Crick

Greetings!

Our team uses a Smartsheet to track monthly invoices for each client. Each invoice date is listed separately for each client (see attached). We are interested in populating in another Smartsheet the date of the last invoice. For some clients, it might be 02/28/2022, for others 12/31/2021, or other dates for other clients.

Last Invoice Date.xlsx

Uploaded Mar 14, 202213.58 KB

Is there a formula in Smartsheet that can pull for each unique client its last invoice date, even if the client name is listed multiple times?

Thank you,

John

John Crick

I should add that the client names are not in alphabetical order. The Smartsheet is sorted by invoice date, then Client name.

Genevieve P.

Hi @John Crick

Yes! You can use a MAX(COLLECT formula to pull the Max Date, based on criteria you specify in the COLLECT function.

ex:

=MAX(COLLECT({Date Column}, {Company Column}, Company@row))

This would need to be put in a Date column in your second sheet, and it's assuming that you have all the client names listed down one column in your Company column already.

Let me know if this works for you!

Cheers,

Genevieve

John Crick

Success! Thank you for the great solution!

John

Genevieve P.

Wonderful! I'm glad I could help. 🙂

John Crick

Genevieve, circling back with a modification to the inquiry above. I would now like to add to the search criteria the last time a client was invoiced for a specific product (see attached). How can this be accomplished?

Last Invoice Date By Product.xlsx

Uploaded Apr 5, 202214.21 KB

Thank you,

John

Genevieve P.

Hi @John Crick

This is where the COLLECT function is so handy... all you have to do is add in the new Column and new Criteria into your formula:

=MAX(COLLECT({Date Column}, {Company Column}, Company@row, {Product Column}, Product@row))

or

=MAX(COLLECT({Date Column}, {Company Column}, Company@row, {Product Column}, "Product"))

Let me know if this makes sense!

Cheers,

Genevieve