SOLVED

Contributor

# Look Up Based on Drop Down

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.

3 Replies
best response confirmed by Hans Vogelaar (MVP)
Solution

# Re: Look Up Based on Drop Down

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)) ) )``````

# Re: Look Up Based on Drop Down

That works perfectly, thank you for the help and notes

# Re: Look Up Based on Drop Down

@catherine9910 , you are welcome