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.

Find more posts tagged with

Accepted answers

All comments

Hollie Green

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.

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.