Home

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

%3CLINGO-SUB%20id%3D%22lingo-sub-478125%22%20slang%3D%22en-US%22%3ETo%20IF%20or%20to%20INDEX%20or%20to%20VLOOKUP%2C%20that%20is%20the%20question%3F%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-478125%22%20slang%3D%22en-US%22%3E%3CP%3EHello%20Folk's%2C%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20want%20to%20create%20a%20simple%20spreadsheet%20that%20returns%20start%20and%20end%20times%20based%20on%20a%20week%20number.%20%26nbsp%3BFor%20example%2C%20I%20have%20a%20five%20week%20cycle%20period%20Week%201%20to%20Week%205%2C%20Monday%20to%20Friday%20with%20each%20day%20showing%20different%20start%20and%20end%20times.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20have%20in%20sheet%201%20a%20data%20validation%20dropdown%20list%20that%20has%20my%20week%20commencing%20dates%20in%20cell%20C5.%20%26nbsp%3BI%20have%20in%20C8%20the%20week%20number%20that%20is%20produced%20using%20a%20simple%20VLOOKUP%20statement.%20based%20on%20the%20selected%20date%20in%20C5.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EIn%20columns%20E6%20to%20H6%20I%20have%20the%20column%20headers%20Day%2C%20From%2C%20To%20and%20Commitment.%20%26nbsp%3BUnder%20these%20headers%20I%20have%20the%20day%20off%20the%20week%2C%20the%20start%20time%2C%20the%20end%20time%20and%20the%20time%20an%20individual%20has%20committed%20two%20either%20start%20or%20end%20their%20day.%20%26nbsp%3BThis%20is%20the%20section%20I%20would%20like%20the%20returned%20data%20to%20be%20located.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EIn%20Columns%20K%20to%20P%20I%20have%20my%20source%20info.%20%26nbsp%3BYou%20can%20see%20my%20sheet%20in%20the%20screenshot%20below.%20%26nbsp%3BI%20haver%20tried%20various%20options%20that%20include%20Indexing%2C%20Vlookup%20and%20counting%20the%20occurrences%20of%20days%20but%20I'm%20failing%20big%20time%20on%20getting%20the%20desired%20results.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20come%20to%20you%20lovely%20talented%20folk%20to%20see%20if%20you%20can%20set%20me%20on%20the%20road%20to%20enlightenment.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThanks%20in%20advance%2C%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EDS.%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-center%22%20style%3D%22width%3A%20999px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Fgxcuf89792.i.lithium.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F109741i945DEC7C28DF8F18%2Fimage-size%2Flarge%3Fv%3D1.0%26amp%3Bpx%3D999%22%20alt%3D%22Screenshot%202019-04-21%20at%2012.52.29.png%22%20title%3D%22Screenshot%202019-04-21%20at%2012.52.29.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-478125%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EBI%20%26amp%3B%20Data%20Analysis%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EExcel%20on%20Mac%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EExcel%20on%20mobile%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EExcel%20Online%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EMacros%20and%20VBA%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3ETraining%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-478293%22%20slang%3D%22en-US%22%3ERe%3A%20To%20IF%20or%20to%20INDEX%20or%20to%20VLOOKUP%2C%20that%20is%20the%20question%3F%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-478293%22%20slang%3D%22en-US%22%3EIn%20F8%3AH12%2C%20use%20the%20following%3A%3CBR%20%2F%3E%3DVLOOKUP(%24E8%26amp%3B%24C%248%2C%24M%247%3A%24P%2431%2C2%2C0)%3CBR%20%2F%3E%3CBR%20%2F%3EYou%E2%80%99ll%20need%20to%20change%20the%202%20to%203%20for%20column%20G%20and%204%20for%20column%20H.%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-478398%22%20slang%3D%22en-US%22%3ERe%3A%20To%20IF%20or%20to%20INDEX%20or%20to%20VLOOKUP%2C%20that%20is%20the%20question%3F%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-478398%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F324557%22%20target%3D%22_blank%22%3E%40DickScrongle%3C%2FA%3E%26nbsp%3B%2C%20you%20don't%20need%20helper%20column%20here.%20Use%20INDEX%2FMATCH%20with%20criteria%20like%3C%2FP%3E%0A%3CPRE%3E%3DINDEX(N%246%3AN%2425%2CMATCH(1%2CINDEX((%24K%246%3A%24K%2425%3D%24C%248)*(%24L%246%3A%24L%2425%3D%24E8)%2C0)%2C0))%3C%2FPRE%3E%0A%3CP%3Ein%20F8%2C%20drag%20to%20the%20right%20and%20down%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-478414%22%20slang%3D%22en-US%22%3ERe%3A%20To%20IF%20or%20to%20INDEX%20or%20to%20VLOOKUP%2C%20that%20is%20the%20question%3F%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-478414%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F324602%22%20target%3D%22_blank%22%3E%40MichaelMays%3C%2FA%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EA%20huge%20thank%20Michael%2C%20your%20suggestion%20worked%20perfectly.%20%26nbsp%3BThank%20you%20for%20your%20time%20in%20assisting%20me%20today.%20%F0%9F%98%83%F0%9F%91%8D%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-478415%22%20slang%3D%22en-US%22%3ERe%3A%20To%20IF%20or%20to%20INDEX%20or%20to%20VLOOKUP%2C%20that%20is%20the%20question%3F%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-478415%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F521%22%20target%3D%22_blank%22%3E%40Sergei%20Baklan%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EHello%20Sergei%2C%20with%20a%20little%20tweak%20this%20also%20worked%20really%20well.%20%26nbsp%3BInteresting%20that%20yours%20and%20%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F324602%22%20target%3D%22_blank%22%3E%40MichaelMays%3C%2FA%3E%20suggestion%20worked%20really%20well%20but%20using%20different%20formulas.%20%26nbsp%3BThank%20you%20for%20your%20time%20today%20Sergei%2C%20I%20really%20appreciate%20it.%20%F0%9F%98%83%F0%9F%91%8D%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-478440%22%20slang%3D%22en-US%22%3ERe%3A%20To%20IF%20or%20to%20INDEX%20or%20to%20VLOOKUP%2C%20that%20is%20the%20question%3F%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-478440%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F324557%22%20target%3D%22_blank%22%3E%40DickScrongle%3C%2FA%3E%26nbsp%3B%2C%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EYou%20are%20welcome.%20In%20Excel%20everything%20could%20be%20done%20by%20few%20several%20ways.%20Select%20one%20which%20you%20better%20understand%2C%20thus%20it'll%20be%20easier%20in%20maintenance.%20And%20try%20other%20approaches%2C%20perhaps%20you%20find%20them%20more%20suitable.%3C%2FP%3E%3C%2FLINGO-BODY%3E
DickScrongle
New 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. 😃👍

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

@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.

Related Conversations
Tabs and Dark Mode
cjc2112 in Discussions on
16 Replies
Stable version of Edge insider browser
HotCakeX in Discussions on
35 Replies
flashing a white screen while open new tab
cntvertex in Discussions on
11 Replies
How to Prevent Teams from Auto-Launch
chenrylee in Microsoft Teams on
28 Replies