Lookup reference in another row referenced

Hello,

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

Find more posts tagged with

Manufacturing

Construction

Formulas

Reports

Project Management

Comments

Paul Charlebois

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.

Paul

Paul Charlebois

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.

Paul

Denise Vance

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.

Thanks,

Denise

Sign in to join the conversation!

Quick Links

Trending discussions

Is there a way to create a 3-digit padding for numbers
Hi all, and thanks in advance! I wanted to know if there was any way to create "padding" in Smartsheet. Basically I have account numbers, and a handful are still 2-digit, so they appear as TDL-17, TDL-67, etc. Most of the accounts are 3-digit numbers like TDL-180. I want something that will recognize whether it is 2 or 3…
Dynamic formula to return date of next Thursday every week.
I'm wondering if anyone has worked out a dynamic formula to return the date of the 'next' Thursday of the month. I have a worksheet where I need to send out a reminder to a contacts in a contact column in the worksheet each Thursday of the current month if a criteria has not been met. When criteria has been met, then…
Check box for date in previous month
Hello, I have the formula to check a box for line items with the current month. =IF(AND(YEAR(Date@row ) = YEAR(TODAY()), MONTH(Date@row ) = MONTH(TODAY())), 1, 0) Trying to set up a report for all items with the previous month (i.e. 1 month prior to current). How can I modify to check the box?