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.4K Get Help
- 424 Global Discussions
- 221 Industry Talk
- 465 Announcements
- 4.8K Ideas & Feature Requests
- 143 Brandfolder
- 144 Just for fun
- 62 Community Job Board
- 463 Show & Tell
- 32 Member Spotlight
- 1 SmartStories
- 301 Events
- 39 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!