Vlookup between 2 dates ?

JumboCactuar

Well-known Member
Joined
Nov 16, 2016
Messages
785
Office Version
  1. 365
Platform
  1. Windows
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
 

Excel Facts

Whats the difference between CONCAT and CONCATENATE?
The newer CONCAT function can reference a range of cells. =CONCATENATE(A1,A2,A3,A4,A5) becomes =CONCAT(A1:A5)
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
 
Upvote 0
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
 
Upvote 0
Re: How can this vlookup be achieved between 2 dates ?

expected result?
 
Upvote 0
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
 
Upvote 0
Re: How can this vlookup be achieved between 2 dates ?

Thankyou @Matty
This looks correct I will try it when I get on PC
 
Upvote 0
Re: How can this vlookup be achieved between 2 dates ?

Ok, I misunderstood

never mind

Have a nice day
 
Upvote 0

Forum statistics

Threads
1,213,535
Messages
6,114,194
Members
448,554
Latest member
Gleisner2

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top