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
-
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)
Answers
-
How many instances of the same Module Number are going to be in the source sheet?
-
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.
Mathieu | Workflow Consultant
info@evolytion.com
-
Quite often. The target sheet has a list of tasks and where those tasks invlove a specific module the Module Number column is populated.
-
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, Thanks do much, that did the trick.
-
Happy to help. 👍️
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Customer Resources
- 64.6K Get Help
- 433 Global Discussions
- 136 Industry Talk
- 468 Announcements
- 4.9K Ideas & Feature Requests
- 143 Brandfolder
- 147 Just for fun
- 64 Community Job Board
- 466 Show & Tell
- 32 Member Spotlight
- 2 SmartStories
- 298 Events
- 36 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!