Formula help

heather.altiero52961

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?

 

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.