To IF or to INDEX or to VLOOKUP, that is the question?

Copper Contributor

Hello Folk's,

 

I want to create a simple spreadsheet that returns start and end times based on a week number.  For example, I have a five week cycle period Week 1 to Week 5, Monday to Friday with each day showing different start and end times.

 

I have in sheet 1 a data validation dropdown list that has my week commencing dates in cell C5.  I have in C8 the week number that is produced using a simple VLOOKUP statement. based on the selected date in C5.

 

In columns E6 to H6 I have the column headers Day, From, To and Commitment.  Under these headers I have the day off the week, the start time, the end time and the time an individual has committed two either start or end their day.  This is the section I would like the returned data to be located.

 

In Columns K to P I have my source info.  You can see my sheet in the screenshot below.  I haver tried various options that include Indexing, Vlookup and counting the occurrences of days but I'm failing big time on getting the desired results.

 

I come to you lovely talented folk to see if you can set me on the road to enlightenment.

 

Thanks in advance,

 

DS.

Screenshot 2019-04-21 at 12.52.29.png

 

 

 

5 Replies
In F8:H12, use the following:
=VLOOKUP($E8&$C$8,$M$7:$P$31,2,0)

You’ll need to change the 2 to 3 for column G and 4 for column H.

@DickScrongle , you don't need helper column here. Use INDEX/MATCH with criteria like

=INDEX(N$6:N$25,MATCH(1,INDEX(($K$6:$K$25=$C$8)*($L$6:$L$25=$E8),0),0))

in F8, drag to the right and down

 

@MichaelMays

 

A huge thank Michael, your suggestion worked perfectly.  Thank you for your time in assisting me today. =):thumbs_up:

@Sergei Baklan 

 

Hello Sergei, with a little tweak this also worked really well.  Interesting that yours and @MichaelMays suggestion worked really well but using different formulas.  Thank you for your time today Sergei, I really appreciate it. =):thumbs_up: 

@DickScrongle ,

 

You are welcome. In Excel everything could be done by few several ways. Select one which you better understand, thus it'll be easier in maintenance. And try other approaches, perhaps you find them more suitable.