Index Collect - Pulling from one matrix to another.

MPrice1
MPrice1 ✭✭
edited 10/27/22 in Formulas and Functions

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

  • Hollie Green
    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.

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!