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
- Customer Resources
- 64.8K Get Help
- 434 Global Discussions
- 138 Industry Talk
- 470 Announcements
- 4.9K Ideas & Feature Requests
- 129 Brandfolder
- 148 Just for fun
- 65 Community Job Board
- 486 Show & Tell
- 33 Member Spotlight
- 2 SmartStories
- 300 Events
- 36 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!