INDEX MATCH With 2 criteria inc. between date

Hi

I am hoping someone can help me... this is my first post so please bear with me

I am writing what will become a job planner so that i can see what jobs my guys are working on on which days. I have almost got there but i am stuck on the INDEX MATCH. I have a raw data tab showing:
RawData.JPG

I then have a separate tab to show the job per person, per day :
Planner.JPG

What i have managed to do is get the job for that person, on that day to show. To get the job number I looked up the person and the date (concatenated) and get the job number - =IFERROR(INDEX(RawData!$A:$I,MATCH($A7&B$5,RawData!$A:$A,0),2),"")

But what i really need to do is to look at the plan date for that person and if that date falls between the start and end date to show the job number. The added complication is that there may be more than one job for that person on that day so i'm not sure how to get around that but for now just the first job on tat day will suffice. I hope i have explained myself well enough but please let me know if i haven't... any help would be gratefully received, thanks

Attach a sample workbook (not image).

Make sure there is just enough data to demonstrate your need. Include a BEFORE sheet and an AFTER sheet in the workbook if needed to show the process you're trying to complete or automate.

Make sure your desired results are shown, mock them up manually if necessary.

Remember to desensitize the data.

Click on GO ADVANCED and then scroll down to Manage Attachments to open the upload window.

Thanks for the reply, apologies I did try to attach the spreadsheet but when i go to Go Advanced and click the Attachments button all i get is a white box, i can't click it or browse so I wasn't sure what to do? I am sure i'm doing something wrong...

Think I just uploaded it... sorry, as I said, i only joined this forum today so it's all still new :-)

Sorry, now attached... i think :-)

I think you will need VBA to do this as there is a need to concatenate several job numbers for one day plus we cannot use the ENGINEER/DATE as a "generic" lookup.

Thanks for the reply, I wonder, as a first port of call, if we just take the first job if there's more than one (which is what i have managed to do currently), forgetting the multiple jobs for now, is there a way we can show that first job number spanning across all of the days between the start and end date?

One way:

In B7 of "Resource Planner AFTER"

=IFERROR(IF(AND(B$4>=VLOOKUP($A7,RawData!$A$2:$E$1000,4,0),B$4<=VLOOKUP($A7,RawData!$A$2:$E$1000,5,0)),VLOOKUP($A7,RawData!$A$2:$E$1000,2,0),""),"")

copy across

NOTE: I changed date to be "proper" Excel dates not Text.

Hello, thanks for this but it doesn't seem to be working for me. I pasted the formula into cell B7 ('Dave Smith' / '01/01/17') on the AFTER tab and dragged it across the entire timespan but I get no results in any cell. I changed the date formats to 'Short Date'. I looked at the formula and it is looking at the the right cells so i'm not sure what's wrong - i attach the amended version with your formula.

I do appreciate your time, thank you

Michelle

It will only work if "Dave Smith" (without the added date) is the name in the "RawData" sheet.

This is the issue with any MATCHing formula: for example, this will find the first occurrence of "Dave Smith" + date

=IFERROR(IF(AND(G$4>=VLOOKUP($A7&"*",RawData!$A$2:$E$999,4,0),G$4<=VLOOKUP($A7&"*",RawData!$A$2:$E$999,5,0)),VLOOKUP($A7&"*",RawData!$A$2:$E$999,2,0),""),"")

If you have mutiple occurrences you (probably) need VBA, especially as you can have several "events" on a single day for a given person.

I note your dates are TEXT: any reason why?

I set the dates as text as i was originally using CONCATENATE with INDEX MATCH and it was showing the date as numbers which caused my match to fail but if we're not using MATCH then we can revert back to having them as proper dates.

Finding the first occurrence is fine, i'm happy with that.

I changed the dates on row 4 to be proper dates and took out the forced TEXT format and pasted in your formula but and i'm still getting no values... well, correction, I am getting "0" against every cell.
Planner 1.JPG

See attached.

ALL dates are Excel dates (not text).

Changed "Dave Smith" entries to give dates in January.

Thanks very much for this, i really do appreciate your help with this,

I can see that 05/01/2017 and 06/01/2017 are showing a job number but no other dates are showing anything even thought 'Dave Smith' has jobs on those days. For example, row 4 on 'RawData' has job '1190-14-DW066' from 07/01/2017 - 19/01/2017 so why isn't that showing? When you say "ALL dates are Excel dates (not text)" do i still need to change the format somewhere?

As I said earlier, the wild card used i.e. A7 & "*" will only find the first occurrence of whatever is in A7 e.g "Dave Smith".

If you want multiple entries you will need to use VBA macros.

I thought by multiple entries you meant it would only how the first job on any given day? That I understood and that was fine. Maybe it is my mis-understanding, and apologies if it is but, from what i understood it would only pull the first job for that day not the first job for that person. I.e If Dave has JOB001 and JOB002 today and JOB003 tomorrow then it would show JOB001 today and JOB003 tomorrow, JOB002 would not be shown. Have I mis-understood this?

Within the "Resource Planner" we can only match on "Dave Smith*" using "*" as a "wildcard. This will only find the first "Dave Smith " entry and add the Job numbers for that entry i.e for 5/6 January in the sample.

As you have about 30 entries for "Dave Smith" we cannot "loop" through (match all) the entries using a formula so the only option is to use VBA macro code.

It is further complicated because "Dave Smith" can have more than one Job Number on any given date.

.

Right, I understand now. Thank you very much for your help with this, it's very much appreciated.

How do you want to proceed?

Well I only have a basic knowledge of VBA so I wouldn't know how to get that working using code so I wouldn't want to waste more of your time as I know I have already taken a lot of time from you...

If you areprepared to have a VBA solution, then mark this as solved and re-post your thread on the VBA/Macro forum.

If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED. Thanks.

Will do, thank you very much :-)