Vlookup between 2 dates ?

[JumboCactuar]

Hi,
Not sure if vlookup is the correct thing to use for this but i have data like below:

[TABLE="width: 500"]
<tbody>[TR]
[TD]Name[/TD]
[TD]StartDate[/TD]
[TD]EndDate[/TD]
[/TR]
[TR]
[TD]John[/TD]
[TD]12/05/2019[/TD]
[TD]15/05/2019[/TD]
[/TR]
[TR]
[TD]Luke[/TD]
[TD]14/05/2019[/TD]
[TD]14/05/2019[/TD]
[/TR]
[TR]
[TD]Anna[/TD]
[TD]11/05/2019[/TD]
[TD]16/05/2019[/TD]
[/TR]
[TR]
[TD]Alex[/TD]
[TD]14/05/2019[/TD]
[TD]16/05/2019[/TD]
[/TR]
</tbody>[/TABLE]

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

[TABLE="width: 500"]
<tbody>[TR]
[TD]11/05/2019[/TD]
[TD]12/05/2019[/TD]
[TD]13/05/2019[/TD]
[TD]14/05/2019[/TD]
[TD]15/05/2019[/TD]
[TD]16/05/2019[/TD]
[/TR]
[TR]
[TD]Anna[/TD]
[TD]John[/TD]
[TD]John[/TD]
[TD]John[/TD]
[TD]John[/TD]
[TD]Anna[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]Anna[/TD]
[TD]Anna[/TD]
[TD]Luke[/TD]
[TD]Anna[/TD]
[TD]Alex[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]Anna[/TD]
[TD]Alex[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]Alex[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]


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

any help appreciated

 

[mole999]

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]

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]

Re: How can this vlookup be achieved between 2 dates ?

expected result?

 

[Matty]

Re: How can this vlookup be achieved between 2 dates ?

Hi,

Given the following exists across range A1:J5:

[TABLE="class: tableizer-table"]
<thead>[TR="class: tableizer-firstrow"]
[TH]Name[/TH]
[TH]StartDate[/TH]
[TH]EndDate[/TH]
[TH] [/TH]
[TH]11/05/2019[/TH]
[TH]12/05/2019[/TH]
[TH]13/05/2019[/TH]
[TH]14/05/2019[/TH]
[TH]15/05/2019[/TH]
[TH]16/05/2019[/TH]
[/TR]
</thead><tbody> [TR]
[TD]John[/TD]
[TD]12/05/2019[/TD]
[TD]15/05/2019[/TD]
[TD] [/TD]
[TD]Anna[/TD]
[TD]John[/TD]
[TD]John[/TD]
[TD]John[/TD]
[TD]John[/TD]
[TD]Anna[/TD]
[/TR]
[TR]
[TD]Luke[/TD]
[TD]14/05/2019[/TD]
[TD]14/05/2019[/TD]
[TD] [/TD]
[TD] [/TD]
[TD]Anna[/TD]
[TD]Anna[/TD]
[TD]Luke[/TD]
[TD]Anna[/TD]
[TD]Alex[/TD]
[/TR]
[TR]
[TD]Anna[/TD]
[TD]11/05/2019[/TD]
[TD]16/05/2019[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD]Anna[/TD]
[TD]Alex[/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]Alex[/TD]
[TD]14/05/2019[/TD]
[TD]16/05/2019[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD]Alex[/TD]
[TD] [/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

Formula in E2 is:

=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]

Re: How can this vlookup be achieved between 2 dates ?

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

 

[JumboCactuar]

Re: How can this vlookup be achieved between 2 dates ?

expected result?

Expected output as the 2nd table I posted, from only the dates in row 1, names output below for each in table 1 range

 

[sandy666]

Re: How can this vlookup be achieved between 2 dates ?

Ok, I misunderstood

never mind

Have a nice day