Vlookup between 2 dates ?

JumboCactuar

Well-known Member
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
 

mole999

Moderator
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
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
 

Matty

Well-known Member
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
 

sandy666

Well-known Member
Re: How can this vlookup be achieved between 2 dates ?

Ok, I misunderstood

never mind

Have a nice day
 

Some videos you may like

This Week's Hot Topics

Top