# Function that looks up a value under each subcategory

Options

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

• ✭✭✭✭✭✭
Options

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)

• ✭✭✭✭✭✭
Options

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!