INDEX Function to lookup a value from another sheet

I need to add a "Need By" date to a sheet based on infomation and criteria from another sheet.

The working sheet has the following relevant columns:

Module (an alphanumeric identifier)

Need By Date (The column I need to populate)

The Target Sheet {LNG - Project Tracker 1} has the following relevant columns:

Module Number (Must match Module for working sheet) {Rande 2}

Task Name (must be "Installation of Equipment") {Range 1}

Start Date (The date I need) {Range 3}

So based of research, and I'm just barely into day 2 of using Smartsheet, I came up with the following formula:

=INDEX({LNG - Project Tracker 1 Range 3}, MATCH(1, ({LNG - Project Tracker 1 Range 1} = "Installation of Equipment") * ([Module Number]1 = {LNG - Project Tracker 1 Range 2}), 0))

I'm getting an #Invalid Operation when using this formula.

Best Answer


Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!