Look Up Based on Drop Down




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!

3 Replies
best response confirmed by Hans Vogelaar (MVP)


If your Excel version supports dynamic arrays it could be

Weekly (entire spill)

  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)

  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)) ) )
That works perfectly, thank you for the help and notes

@catherine9910 , you are welcome