INDEX MATCH Function with MAX?

Donny
Donny
edited 01/17/25 in Formulas and Functions

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

  • Kerry St. Thomas
    Kerry St. Thomas Community Champion
    edited 01/17/25

    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:
    https://help.smartsheet.com/function/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

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!