Function that looks up a value under each subcategory

Hi guys

Hope you are well

I was wondering if you can maybe help me find a function that will solve my problem.

In my one sheet my expenses are categorized, and under each category is then subcategorized according to the months. I would like to link/look up these values on a different sheet. My problem is that it needs to get the value for March for example and then look up each category's March value to the appropriate category. Why I think something like a VLOOKUP won’t work is because in my description column there will be multiple names for each month. I hope I am making sense.


Example:

Sheet 1:

Steel:

              Feb:      X

              Mar:     Y

              April:     Z

Wages:

Feb:      A

              Mar:     B

              April:     C


Sheet 2:

Description        Feb       Mar      April     

Steel                    X            Y            Z

Wages                 A            B            C


I need sheet 2 to read each month’s value from sheet 1 without having to do a cell link as the number of cells is a lot.

Please let me know if you can maybe help me.

Kind regards

Best Answer

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭
    Answer ✓

    You would need a helper column on the source sheet that pulls in the parent row data onto each of the child rows (Steel, Wages, etc.).


    From there you would use an INDEX/COLLECT like so...

    =INDEX(COLLECT({Number Column}, {Helper Column}, @cell = "Steel", {Month Column}, @cell = "Feb"), 1)

Answers

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭
    Answer ✓

    You would need a helper column on the source sheet that pulls in the parent row data onto each of the child rows (Steel, Wages, etc.).


    From there you would use an INDEX/COLLECT like so...

    =INDEX(COLLECT({Number Column}, {Helper Column}, @cell = "Steel", {Month Column}, @cell = "Feb"), 1)

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!