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
-
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
-
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
Categories
Check out the Formula Handbook template!