Formula to get the last value in a data range
Is there a work around that will provide you with the last value in a data range instead of the first as vlookup would do? Also, we would need to ignore any cells that were blank.
Best Answer
-
OK, fixed it....
First... You need to create a Helper Column I called it "CurrContrMatch". The formula in that column will be:
=[Contract List]@row + " " + [Contract Mods]@row
THEN, your formula for the Current Contract Value would be:
=INDEX([Contract Value]:[Contract Value], MATCH(([Contract Number]@row + " " + MAX(COLLECT([Contract Mods]:[Contract Mods], [Contract List]:[Contract List], [Contract Number]@row))), CurrContrMatch:CurrContrMatch, 0))
You can hide the CurrContrMatch column once you create and test your formulas (recommend making it a Column Formula so it doesn't accidentally get broken).
Answers
-
Instead of VLOOKUP, try using MAX(COLLECT([Date to Collect Range}; {Date to collect range}; ISDATE(@cell)))
Should do the trick.
-
Hi @David Joyeuse ,
Thanks for your feedback. I'm having trouble getting that to work. I'll post an example of a situation where I'm looking to get the current value of a contract which has had 4 modifications. In the example the value went up, was unchanged and left blank and then went down.
Thanks Again.
-
Try this:
=INDEX([Contract Value]:[Contract Value], MATCH(MAX(COLLECT([Contract Mods]:[Contract Mods], [Contract List]:[Contract List], [Contract Number]@row)), [Contract Mods]:[Contract Mods], 0))
-
Oops - there's a problem with my formula, but I need to go into a meeting. I'll work on fixing it when my meeting is done (sorry!)
-
OK, fixed it....
First... You need to create a Helper Column I called it "CurrContrMatch". The formula in that column will be:
=[Contract List]@row + " " + [Contract Mods]@row
THEN, your formula for the Current Contract Value would be:
=INDEX([Contract Value]:[Contract Value], MATCH(([Contract Number]@row + " " + MAX(COLLECT([Contract Mods]:[Contract Mods], [Contract List]:[Contract List], [Contract Number]@row))), CurrContrMatch:CurrContrMatch, 0))
You can hide the CurrContrMatch column once you create and test your formulas (recommend making it a Column Formula so it doesn't accidentally get broken).
-
Thanks @MCorbin , that did the trick.
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 63K 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
- 303 Events
- 34 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!