Look Up Based on Drop Down

[catherine9910]

Hello,

I have attached a workbook that I need help with. The instructions on what I am looking for are in the workbook. Also, if it is not too much to ask can you also explain each part of the formula you used. I am trying to learn the different formulas.

Thank you in advance!

[#2 · Accepted solution]

@catherine9910 

If your Excel version supports dynamic arrays it could be

Weekly (entire spill)

=LET(
  month, XMATCH(K11,C:C),
  week, XMATCH("Week " & N11, INDEX(B:B,month):INDEX(B:B,100) ) + month -1,
  INDEX(D:D, week +1, 1):INDEX(H:H, week+2) )

Monthly (in K8 and drag it to the right and down)

=LET(
  month, XMATCH($K$11,$C:$C),
  weeks, INDEX(D:D, month):INDEX(D:D,month+20),
 SUM( INDEX(weeks,SEQUENCE(5,, 2+ ROW() - ROW($J$7), 4)) ) )

[#3]

That works perfectly, thank you for the help and notes

[#4]

@catherine9910 , you are welcome