How can I get the last matching value on Smartsheet?

Tvl_97

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

Toufong Vang

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)

Toufong Vang

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)