How can I get the last matching value on Smartsheet?

Options

Hey everyone,

I would like to find a formula that would help me find the last matching value. I can do it in excel using the index/match function but I don't think it works on Smartsheet.

Columns 1 and 2 are given whereas column 3 is the desired output.

Note: Column 3 was done manually, but I would like a formula that I can use to do it for me


Best Answer

  • Toufong Vang
    Toufong Vang ✭✭✭✭✭
    Answer ✓
    Options

    If "last matching value" is the largest value in Amount, then...

    =MAX(COLLECT(Amount:Amount, [Group Name]:[Group Name], =[Group Name]@row))

    However, if "last matching value" is the most recently/newly entered value in Amount, then you'll need a helper column. In this example, that column is AutoNum and contains an auto-number value.

    =INDEX(AutoNum:Amount, MATCH( MAX(COLLECT(AutoNum:AutoNum, [Group Name]:[Group Name],=[Group Name]@row)), AutoNum:AutoNum, 0),3)

    Explanation (For when looking for most recent in Amount)

    Here is the basic structure of the formula using the function INDEX( range, row, column)...

    =INDEX( AutoNum:Amount, __ , 3)

    • The value you want is in the 3rd column.

    • You need to identify the row from the range where [Group Name]:[Group Name] is the same as that of the one in the current row AND the value of AutoNum is the largest.

    The most recently entry will have the highest AutoNum value. The expression below returns the highest_AutoNum_value for the Group Name set.

    MAX(COLLECT(AutoNum:AutoNum, [Group Name]:[Group Name],=[Group Name]@row)) 

    Use MATCH( highest_AutoNum_value, AutoNum:AutoNum, 0) to retrieve the row where it is found. Which looks like the following...

    MATCH( MAX(COLLECT(AutoNum:AutoNum, [Group Name]:[Group Name],=[Group Name]@row)), AutoNum:AutoNum, 0)

    ...now plug that "row" into the INDEX() function...

    =INDEX( AutoNum:Amount, __ , 3) which ends up looking like...

    =INDEX(AutoNum:Amount, MATCH( MAX(COLLECT(AutoNum:AutoNum, [Group Name]:[Group Name],=[Group Name]@row)), AutoNum:AutoNum, 0),3)

Answers

  • Toufong Vang
    Toufong Vang ✭✭✭✭✭
    Answer ✓
    Options

    If "last matching value" is the largest value in Amount, then...

    =MAX(COLLECT(Amount:Amount, [Group Name]:[Group Name], =[Group Name]@row))

    However, if "last matching value" is the most recently/newly entered value in Amount, then you'll need a helper column. In this example, that column is AutoNum and contains an auto-number value.

    =INDEX(AutoNum:Amount, MATCH( MAX(COLLECT(AutoNum:AutoNum, [Group Name]:[Group Name],=[Group Name]@row)), AutoNum:AutoNum, 0),3)

    Explanation (For when looking for most recent in Amount)

    Here is the basic structure of the formula using the function INDEX( range, row, column)...

    =INDEX( AutoNum:Amount, __ , 3)

    • The value you want is in the 3rd column.

    • You need to identify the row from the range where [Group Name]:[Group Name] is the same as that of the one in the current row AND the value of AutoNum is the largest.

    The most recently entry will have the highest AutoNum value. The expression below returns the highest_AutoNum_value for the Group Name set.

    MAX(COLLECT(AutoNum:AutoNum, [Group Name]:[Group Name],=[Group Name]@row)) 

    Use MATCH( highest_AutoNum_value, AutoNum:AutoNum, 0) to retrieve the row where it is found. Which looks like the following...

    MATCH( MAX(COLLECT(AutoNum:AutoNum, [Group Name]:[Group Name],=[Group Name]@row)), AutoNum:AutoNum, 0)

    ...now plug that "row" into the INDEX() function...

    =INDEX( AutoNum:Amount, __ , 3) which ends up looking like...

    =INDEX(AutoNum:Amount, MATCH( MAX(COLLECT(AutoNum:AutoNum, [Group Name]:[Group Name],=[Group Name]@row)), AutoNum:AutoNum, 0),3)

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!