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.

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!!

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!!