Function that looks up a value under each subcategory

Andre Steyn

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

Paul Newcome

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)

Paul Newcome

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)