Formula help

Hello, I am trying to do a vlookup on my smartsheet task list to reference a different smartsheet which looks up for example "312" returns the value two columns over, which is a date, and then minus 90 days from that date and returns that date in the cell. Is there a way to do this?

Find more posts tagged with

Formulas

Comments

Paul Newcome

=(VLOOKUP([Column Name]@row, {Reference Sheet Range 1}, 3) - 90)

For the reference sheet range, you would start with the leftmost column being the one containing the 312 and the rightmost column being the date.

The 3 in the formula is the column number of the data you want to pull from your table with the leftmost column being 1.

From there simply subtract 90, and you're all set. Just make sure the target column (the one where you have the formula) is a Date type column.

heather.altiero52961

Thank you! My formula is returning no match when there is a match. can you please help?

The value I am looking up is column 38 row 6, referencing the date column in sheet 2.

=(VLOOKUP([Column38]6, {date}, 9) - 90)

Paul Newcome

When you reference another sheet, the easiest way is to use the "Reference Another Sheet" link in the formula help box. Select your range from there and click "Insert Reference

Based on the second image below I would use 4 as my column reference after the sheet reference denoting that it is the 4th column in the RANGE, not necessarily the 4th column in the sheet.

Sign in to join the conversation!

Quick Links

Trending discussions

Match or Index
I am trying to pull a phone number and email from our master roster that is on another sheet in Smartsheet. I cannot figure out the formula. This is the formula I have tried. =INDEX([EMPLOYEE NAME]@row , MATCH({IMS Master Roster Range 2}), 0)
Workflow Generate Impact Report PDF in Impact Form Response Sheet has become invalid
Hi All, I keep getting this annoying error when I try to trigger a PDF generation. I initially set up to trigger the PDF generation to kick off once the last field the needs data is "not blank". That is when the error started. I modified the sheet and added a yes/no drop down to trigger the PDF generation when the value in…
INDEX COLLECT MIN
Desired result is to pull the Initiative Name where the Days to Due Date is the least (so, due the soonest); then to make an index for the Top 10 Initiatives Due Soonest Currently using this formula: =INDEX(COLLECT({Project Master - Initiative}, {Project Master - Days to Due}, MIN({Project Master - Days to Due})), 1) This…