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

A multiple SUMIF function
My current formula is below. I would like to update this so that I only get a total of those that have been Quoted and have a timeframe data of 2025. How do I add this extra filter into the formula? =SUMIF(Status:Status, "Quoted", [Est. GP]:[Est. GP])
Missing column in Report if I use a formula
I'm not able to "view by" a field that I have in my sheets - Quarters (Q1, Q2, Q3, Q4) - in my report. I think it is because I have used a formula to calculate the quarters in my sheets. Does anyone know a way to get a formula based field on the dropdown for view by in my report?
SUMIF function not working
=SUMIFS({GM Compliance Trained}, {GM Compliance Training Dept}, Department@row , {Compliance Training Date}, MONTH(7, 8, 9)) GM Compliance Trained is the number of employees trained. I am trying to sum the number of employees trained in each department during 3rd quarter (July 1-Sep 30). Thinking I need an IFERROR in there…