Formula Help! INDEX(COLLECT)
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.
Best Answers
-
@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!:)
-
Thank you!!
Answers
-
@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)
-
That is coming back as Unparseable
-
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)
-
@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!:)
-
Thank you!!
Help Article Resources
Categories
Check out the Formula Handbook template!
