Enhance the VLOOKUP function to take a date, using it to lookup the value in the cell history

Peter Spung
Peter Spung ✭✭✭
edited 03/14/23 in Smartsheet Basics

Sorry if this is a duplicate. I can see the Idea that I created earlier today in my notifications. It states that the status has changed. But when I click on it (https://community.smartsheet.com/discussion/102567), it says the discussion can not be found. So, here I go again....

We have % Complete and Ranking (Top 20, from 1 to 20) as two cols in a grid. We want to report on those over time in another grid. For example, the current % and ranking now, those values one month ago, two months ago, and three months ago. Now is easy, using VLOOKUP. Looking them up in the past is manual -- one month ago, two months ago, three months ago. I go to the % Complete and Ranking cell for each row, and view the cell history, and determine what it was one, two, and three months ago.

Instead of manually, this new idea is that VLOOKUP take an optional date parameter. If the data is specified, it would lookup the value on that date.

Here is an example of how this feature would be used. In the following grid (a stalled roadmap item report), cols 2, 4 and 5 use VLOOKUP today. The highlighted columns, 6 thru 11, are manually looked up using the cell history on the same cells as cols 4 and 5. With this enhancement, cols 6 thru 11 could also be looked up using the VLOOKUP, supplying the desired dates: one month ago, two months ago, three months ago.

Thanks for listening, and I hope this goes through this time. Peter Spung