How can I get the last matching value on Smartsheet?
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
-
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
-
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
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 62.9K Get Help
- 379 Global Discussions
- 212 Industry Talk
- 442 Announcements
- 4.6K Ideas & Feature Requests
- 140 Brandfolder
- 129 Just for fun
- 130 Community Job Board
- 449 Show & Tell
- 30 Member Spotlight
- 1 SmartStories
- 302 Events
- 33 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!