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

Options
✭✭✭

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.

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

Tags:

• ✭✭✭
Options

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

• Employee
Options

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

October 8 - 10, Seattle, WA | Register now

• ✭✭✭
Options

Success! Thank you for the great solution!

John

• Employee
Options

Wonderful! I'm glad I could help. 🙂

October 8 - 10, Seattle, WA | Register now

• ✭✭✭
Options

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?

Thank you,

John

• Employee
Options

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