Macro that finds value given 3 conditions

I need help creating a macro that finds a value given 3 conditions, year, quarter and color. I realize that the easiest way to solve this is by creating a pivot table, but I was asked to solve this using a macro.

Here is what the table looks like, I need to be able to find the value in C12 given the conditions around it.

table

I appreciate your time and any advice would be pleasantly taken.

1 answer

  • answered 2018-10-16 07:12 Pᴇʜ

    You can use the following formula for the worksheet in your question.

    =OFFSET($B$2,MATCH($B$12,$B$2:$B$7,0)-1,MATCH($C$11,OFFSET($C$3:$F$3,0,MATCH($C$10,$2:$2,0)-COLUMN($B$2)-1),0)+MATCH($C$10,$2:$2,0)-COLUMN($B$2)-1)