Vlookup between 2 dates ?

JumboCactuar

Well-known Member
Joined
Nov 16, 2016
Messages
661
Hi,
Not sure if vlookup is the correct thing to use for this but i have data like below:

NameStartDateEndDate
John12/05/201915/05/2019
Luke14/05/201914/05/2019
Anna11/05/201916/05/2019
Alex14/05/201916/05/2019

<tbody>
</tbody>

Now for example if a have a sheet with dates if row 1

11/05/201912/05/201913/05/201914/05/201915/05/201916/05/2019
AnnaJohnJohnJohnJohnAnna
AnnaAnnaLukeAnnaAlex
AnnaAlex
Alex

<tbody>
</tbody>


Any idea how this could be done (formula / powerquery or vba) ?

any help appreciated
 

Some videos you may like

Excel Facts

Wildcard in VLOOKUP
Use =VLOOKUP("Apple*" to find apple, Apple, or applesauce

mole999

Moderator
Joined
Oct 23, 2004
Messages
10,103
Office Version
2019, 2016, 2013
Platform
Windows
Re: How can this vlookup be achieved between 2 dates ?

vlookup will only return the first answer, so if you expect more then that isn't the route to persue, it allows prefers a sorted data list
 

JumboCactuar

Well-known Member
Joined
Nov 16, 2016
Messages
661
Re: How can this vlookup be achieved between 2 dates ?

vlookup will only return the first answer, so if you expect more then that isn't the route to persue, it allows prefers a sorted data list
Ye vlookup isn't the right thing for this, in the 2nd table I'm wanting to output the names for each date.

I think I maybe need an array or an helper column in the first table
 

sandy666

Well-known Member
Joined
Oct 24, 2015
Messages
4,681
Re: How can this vlookup be achieved between 2 dates ?

expected result?
 

Matty

Well-known Member
Joined
Feb 17, 2007
Messages
3,709
Re: How can this vlookup be achieved between 2 dates ?

Hi,

Given the following exists across range A1:J5:

NameStartDateEndDate 11/05/201912/05/201913/05/201914/05/201915/05/201916/05/2019
John12/05/201915/05/2019 AnnaJohnJohnJohnJohnAnna
Luke14/05/201914/05/2019 AnnaAnnaLukeAnnaAlex
Anna11/05/201916/05/2019 AnnaAlex
Alex14/05/201916/05/2019 Alex

<thead>
</thead><tbody>
</tbody>

Formula in E2 is:

Code:
=IFERROR(INDEX($A$2:$A$5,SMALL(IF(E$1>=$B$2:$B$5,IF(E$1<=$C$2:$C$5,ROW($A$2:$A$5)-ROW($A$2)+1)),ROWS(E$2:E2))),"")
Copied down and across.

Note: this formula requires array processing, so needs to be committed with CTRL+SHIFT+ENTER.

Matty
 

JumboCactuar

Well-known Member
Joined
Nov 16, 2016
Messages
661
Re: How can this vlookup be achieved between 2 dates ?

Thankyou @Matty
This looks correct I will try it when I get on PC
 

sandy666

Well-known Member
Joined
Oct 24, 2015
Messages
4,681
Re: How can this vlookup be achieved between 2 dates ?

Ok, I misunderstood

never mind

Have a nice day
 

Forum statistics

Threads
1,089,607
Messages
5,409,259
Members
403,257
Latest member
Larry Light

This Week's Hot Topics

  • help please
    SORRY NOT ANY GOOD AT EXCEL SO HELP WOULD BE MUCH APPRECIATED this formula is in a sheet called ignore...
  • two formulas needed
    Hello, I'll try my best to explain this: First formula needed in Sheet1 cell A2: If Sheet1 cell B2 = Sheet2 cell B2 then return a 1. If not then...
  • Dynamic Counts
    Good afternoon, we are tidying up some data & the data seems to be growing quicker than we are tidying it up! What we confirm (by reviewing it...
  • Help Excel formula eliminate duplicate values and keep only 2 identical rows.
    as picture below column A has a duplicate value. but the values are not the same as the rule. sometimes 4 rows, sometimes 10 rows or 7 or 9...
  • Macro Compile Error Sub or Function not defined
    Hello, I am trying to run macros from a validation list, all macros have been created and run perfectly on there own but I'm getting a compile...
  • Last row combined with Current Region VBA
    I'm generally happy finding the last row of data through something like Lastrow = Cells(Rows.Count, "D").End(xlUp) but I don't always receive data...
Top