vlookup formula - need help
I am using an excel template and am trying to modify it. I have a sheet "daily schedule" which has a date on it and a to-do section with cells below it. I have a second "task's schedule" sheet that has a column for the date and a column for the task. I am trying to make it so that I can enter in tasks on the task sheet and they will auto fill on the daily sheet. my formula is:
=VLOOKUP(F3,'Task''s Scheduler'!E5:F21,2)
f3 is the date on the daily schedule sheet
e5:f21 is the table on tasks sheet which has the date in column E and tasks in column F.
My only issue is that in the cells under to-do list where the formula goes generates the same result or task for each one. Instead of each one showing a different task. I am not sure how to solve this issue. Any help would be very appreciated.
I have attached the excel template I am using in case that helps it make more sense.
I am not very good at excel things but have been trying. Thank you for reading!
| Attachment | Size |
|---|---|
| Weekley Planner.xlsx | 162.66 KB |
Solution
Hello B.Bunny. There is a solution to your problem but you will need to make an adjustment to your spreadsheet. On your daily schedule sheet you will need to remove the merged cells in column "M", since the formula you are looking for will not work in merged cells. Once you have unmerged cells M4-M36 place this formula in cells M4:
=IFERROR(INDEX('Task''s Scheduler'!$F$5:$F$21,SMALL(IF(DateVal='Task''s Scheduler'!$E$5:$E$21,ROW('Task''s Scheduler'!$E$5:$E$21)-ROW('Task''s Scheduler'!$E$5)+1),ROW(1:1))),"")
Once that is placed within the cell, press cntrl+shift+enter prior to leaving the cell. This sets up the formula as an array. You will know this has been done correctly when brackets are placed around the formula like this:
Once that is done. Select the first cell and drag down the formula using the autofill tool (black box at the bottom right corner of the celected cell, press it and drag it to the last cell of your list.)
This should do the trick for you like the above picture.
Let me know if you have any questions.
Sincerely,
-Max
Second question
Thank you Max! That was such a huge help and I really appreciate it. I do have another question. I changed the layout a bit because I didn't like the long section with each time throughout the day. So how do I change where the formula gets the date from?
I would like the "To-Do's" to correlate with the dates on "week at a glance".
So I need the formula to use the date from F9 for the first day, f15, f21, f26, f31, f36, f41.
I don't know how to attach another spread to show where everything is. I hope this makes sense.
Again thank you for everything!