Forum Discussion
VLOOKUP: ARRANGE A SCHEDULE OF TASKS ON THE SAME DATE
Hi paoloambro
Please see attached the updated worksheet.
I converted the data range to a table named "Data" and I used the following formula.
{=IFERROR(INDEX(Data, SMALL(IF($DE$2=Data[DATE], ROW(Data[TIME])-ROW(Data[[#Headers],[TIME]])), ROW(1:1)),4),"" )}entered as array pressing Ctrl+Shift+Enter.
Cheers,
Hi paoloambro
VLOOKUP will only return 1 entry.
The better approach will be to use INDEX, MATCH, SMALL & ROW functions.
If you can share the file with dummy values, that will be easier to work with.
Cheers,
Hi wumolad
thank you for getting back to me.
Pls find in the attachment the file which includes:
- the source (CO is the first column
- the week days into i need to order all the tasks, day by day
Should you need further info, just let me know.
Thank you so much!
Paolo
Hi paoloambro
Please see attached the updated worksheet.
I converted the data range to a table named "Data" and I used the following formula.
{=IFERROR(INDEX(Data, SMALL(IF($DE$2=Data[DATE], ROW(Data[TIME])-ROW(Data[[#Headers],[TIME]])), ROW(1:1)),4),"" )}entered as array pressing Ctrl+Shift+Enter.
Cheers,
Hi wumolad,
this is really awesome, thank you so much for your help!
It looks like time is not in ascending order:
do you have a clue how to order the time 'smallest to largest'? Do I have to go to Sort&Filter or is there an additional input?
You're of great help. Thank you!
Paolo
Hi paoloambro
The fastest solution which will not require additional formula is to sort the Data by Date, then time. [I have already done that in the attached]
In the alternative, you can use SORT function if you have office 365 subscription.
Cheers.