INDEX MATCH Function with MAX?

Hey, i'm trying to pull the most recent date associated with specific customers from "Sheet 1" so that when I type the specific customers name in the "Customer Name Sheet" column the most recent date associated with that customer gets pulled from sheet 1 to "Previous Attended Tech Date" Column in sheet 2.
This is where i'm at …
=MAX(INDEX({Previous Attended Dates Sheet 1}), MATCH([Customer Name Sheet]@row, {Customer Names Sheets 1}))
I keep getting #INCORRECT ARGUMENT SET.
Screenshots attached for reference, Thanks in Advance.
Answers
-
Try COLLECT - it's up there on my list of favorite Smartsheet functions. The logic of COLLECT is essentially to assemble all the cells in the range of the first clause, where rows of the range in the second clause match the criterion of the third clause. It's like filtering to the name, then looking at the visible date column. COLLECT is designed to be embedded in other functions (like MAX in this case.)
=MAX(COLLECT({Previous Attending Date Entire Column Range},{Previous History Customer Name Entire Column Range},[Current Customer Name Sheet]@row))
For more info on COLLECT:
Good luck!
If this answer resolves your question, please help the Community by marking it as an accepted answer. I'd also be grateful for your response - "Insightful"or "Awesome" reactions are much appreciated. Thanks!
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Customer Resources
- 66.1K Get Help
- 430 Global Discussions
- 149 Industry Talk
- 490 Announcements
- 5.2K Ideas & Feature Requests
- 85 Brandfolder
- 154 Just for fun
- 74 Community Job Board
- 499 Show & Tell
- 33 Member Spotlight
- 2 SmartStories
- 305 Events
- 36 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!