Formula Help! INDEX(COLLECT)

Brandon Sills

I have two sheets and two columns i am trying to compare:

1) Finished Lot Inventory Summary

Column Name: Development

Column Name: Lot Number

Column Name: Finished Lot Inventory Count

2) Starts Schedule

Column Name: Subdivision

Column Name: Lot

Column Name: Notify

On the 'Starts Schedule' sheet, I am trying to populate the column 'Notify' with the value from the column 'Finished Lot Inventory Count' if the Subdivision and Lot within that row matches a Development and Lot Number within the 'Finished Lot Inventory Summary' sheet.

The formula i am using is:

Any tips would be much appreciated.

Find more posts tagged with

Formulas

Accepted answers

brianschmidt

https://community.smartsheet.com/discussion/comment/426862#Comment_426862

@Brandon Sills As a reminder, you don't need [] brackets when the column name you are referencing is one word. Otherwise, it will return unparseable.

Hope this helps!:)

Brandon Sills

Thank you!!

All comments

JamesB

@Brandon Sills You are close. Try this...

=INDEX(COLLECT([UHG Finished Lot Inventory Summary - Count],[UHG Finished Lot Inventory Summary - Development},[Subdivision]@row,{UHG Finished Lot Inventory Summary - Lot #},[Lot]@row),1,0)

Brandon Sills

That is coming back as Unparseable

brianschmidt

I think JamesB is close, but added more than needed and missed taking out some additional brackets you had that you don't need. I would try this:

=INDEX(COLLECT({UHG Finished Lot Inventory Summary - Count},{UHG Finished Lot Inventory Summary - Development},Subdivision@row,{UHG Finished Lot Inventory Summary - Lot #},Lot@row),1)

brianschmidt

https://community.smartsheet.com/discussion/comment/426862#Comment_426862

@Brandon Sills As a reminder, you don't need [] brackets when the column name you are referencing is one word. Otherwise, it will return unparseable.

Hope this helps!:)

Brandon Sills

Thank you!!

Sign in to join the conversation!

Quick Links

Component react.discussion.discussions had an error.

ref must be one of: categoryID, siteSectionID, category, category/categoryID, category/name, category/description, category/url, category/allowedDiscussionTypes, locale, siteSection, siteSection/basePath, siteSection/contentLocale, siteSection/sectionGroup, siteSection/sectionID, siteSection/name, siteSection/description, siteSection/apps, siteSection/attributes, layoutViewType, discussionID, commentID, page, sort, discussion, discussion/name, tags, breadcrumbs, discussionApiParams, serverDraftID, serverDraft.