• 470K Members
• 5,912 Online
• 568K Conversations

New Contributor

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

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.

5 Replies

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

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.

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

@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

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

@MichaelMays

A huge thank Michael, your suggestion worked perfectly.  Thank you for your time in assisting me today. 😃👍

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

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. 😃👍

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

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.

Related Conversations
Tabs and Dark Mode
cjc2112 in Discussions on
48 Replies
Extentions Synchronization
Deleted in Discussions on
3 Replies
Stable version of Edge insider browser
HotCakeX in Discussions on
35 Replies
How to Prevent Teams from Auto-Launch
chenrylee in Microsoft Teams on
30 Replies
flashing a white screen while open new tab
Deleted in Discussions on
14 Replies
Security Community Webinars
Valon_Kolica in Security, Privacy & Compliance on
13 Replies