Lookup reference in another row referenced


Possibly this cannot be done in Smartsheet. I am trying to port over our drawing list coordination sheet from excel to Smartsheet.

The formula uses Lookup in Excel - actual revision number to find on the same row that number - then return the date of that column from the first row of the sheet.

The excel formula is as follows: =LOOKUP($D128,$H128:$BC128,$H$2:$BC$2)

My attempts have failed in SmartSheet.

Can we use LOOKUP to return the result of data from the same column on a different row? 

From my research, there is a requirement to specify which column contains the information, can this be replaced by an argument?

I have attached the Excel Screenshot

Many thank for suggestions




  • Paul Charlebois
    edited 04/09/18

    This is the Smartsheet WIP version

    Formula is : =VLOOKUP([Actual revision]4, DATE4:[Insert Left Placeholder]4, DATE1:[Insert Left Placeholder]1)

    Drawing version 2 - found in the same row, returning the Date value of top Row of the same column.


    Maybe I am overthinking or need to rework the sheet itself.




  • Well, I looked at my Smartsheet differently today and worked out a way to get things to work using INDEX and MATCH in the same formula on different ranges of cells. If anyone wants more information just ask.



  • HiPaul,


    I am trying to do something simular, I want to be able to pick a date from a list. I am intrested on how you set this up using INDEX and MATCH.





Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!