Index Collect - Pulling from one matrix to another.
Whoops! It posted without text. LOL
I've got data that's in a matrix. With Date on the left, and the Product Name in a helper row at the top.
I have an entry form with this layout, and an output form with the exact layout.
I need to match the Date/product to pull the value from the entry form, into a formula in the output sheet, and then run some calculations from there. For now I just need to find the right combination of index/collect to get me the number.
Recommendations for the formula, and the best placement of ranges would be appreciated.
Mockup below. I do have data in columns 1-5 of both sheets, so I don't think Vlookup will work.
Answers
-
I don't think you need the helper row since each of your products are on a different column. I would do something like this.
=Index(Collect({Product 1},{Date},[date]@row)
You would just change your Product reference in the next column over to bring in that product in your output sheet.
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Customer Resources
- 64.9K Get Help
- 441 Global Discussions
- 139 Industry Talk
- 472 Announcements
- 4.9K Ideas & Feature Requests
- 129 Brandfolder
- 148 Just for fun
- 68 Community Job Board
- 496 Show & Tell
- 33 Member Spotlight
- 2 SmartStories
- 300 Events
- 36 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!