INDEX Function to lookup a value from another sheet

James Chestnut

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.

Paul Newcome

Ok. Try an INDEX/COLLECT instead so that you can include the additional ranges and their criteria.

=INDEX(COLLECT({LNG - Project Tracker 1 Range 3}, ({LNG - Project Tracker 1 Range 1}, "Installation of Equipment", {LNG - Project Tracker 1 Range 2}, [Module Number]@row), 1)

Paul Newcome

How many instances of the same Module Number are going to be in the source sheet?

Mathieu PERSICO

Hi James,

From what I understood, you should not considerate the Task Name column. When you reference another sheet, you should only select one column at a time. Try this formula in your Need By column:

=INDEX({LNG - column Start Date}, MATCH(Module@row, {LNG - column Module Number} , 0))

It will be looking for the Module value in the LNG Module Number column and then, the corresponding Start Date value will be returned in the Need by column.

Tell me if it is what you are asking for.

James Chestnut

Quite often. The target sheet has a list of tasks and where those tasks invlove a specific module the Module Number column is populated.

Paul Newcome

Ok. Try an INDEX/COLLECT instead so that you can include the additional ranges and their criteria.

=INDEX(COLLECT({LNG - Project Tracker 1 Range 3}, ({LNG - Project Tracker 1 Range 1}, "Installation of Equipment", {LNG - Project Tracker 1 Range 2}, [Module Number]@row), 1)

James Chestnut

Paul, Thanks do much, that did the trick.

Paul Newcome

Happy to help. 👍️