Forum Discussion

Copper Contributor

May 11, 2020

Solved

VLOOKUP: ARRANGE A SCHEDULE OF TASKS ON THE SAME DATE

Hi, I have a 2016 Excel for Mac and want to organise a day schedule picking datas from a month one (both in the same worksheet). So far I could make it work with only one task per day. If my lookup_...

wumolad
May 11, 2020
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,
DQ39 Report.xlsx54 KB

Iron Contributor

May 11, 2020

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,

paoloambro
Copper Contributor
May 11, 2020
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
DQ39 Report.xlsx29 KB
- wumolad
  Iron Contributor
  May 11, 2020
  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,
  
  DQ39 Report.xlsx54 KB
  - paoloambro
    Copper Contributor
    May 11, 2020
    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

Resources