vLookup: Date

[mauricem72]

I have a list of movies and the corresponding date of release. I'd like to do a vLookup to find any movies that correspond to the date but not the year.

Here's a sample table
<style type="text/css"><!--td {border: 1px solid #ccc;}br {mso-data-placement:same-cell;}--></style>

Alien vs. Predator (2004)	August 13, 2004
Star Wars: Episode III - Revenge of the Sith (2005)	May 19, 2005
Superman Returns (2006)	June 28, 2006
Spider-Man 3 (2007)	May 4, 2007
The Invasion (2007)	August 17, 2007
I Am Legend (2007)	14 December 2007
Aliens vs. Predator: Requiem (2007)	December 25, 2007

<colgroup><col style="width: 350px"><col width="125"></colgroup><tbody>
</tbody>

For example, I'd like is to show "Spider-Man 3" (2007) on May 4, 2018.

I've tried do run a vLookup formula but it ignores the date. Here's what I tried:

=vlookup("5/4/"&"*",Import_Movie!D1:F100,3,FALSE)
​

Any suggestions would be appreciated.

Thanks

 

[BiocideJ]

Two methods:

First method:
Use an Array formula using INDEX/MATCH
=INDEX([FONT=&quot]Import_Movie![/FONT]F1:F100,MATCH("0504",TEXT([FONT=&quot]Import_Movie![/FONT]D1:D100,"MMDD"),0))
(CONFIRM THIS FORMULA BY PRESSING CTRL+SHIFT+ENTER)

PRO: No changes needed to the underlying data
CON: a bit more complicated formula and will need to always confirm with CTRL+SHIFT+ENTER


Second Method:
Add a 'helper' column to the Import Movie tab
Add a NEW column to the Import Movie tab which contains only the MONTH/DATE information
This new column should basically be =TEXT(D2,"MMDD")
The new VLOOKUP formula would then be =VLOOKUP("0504",Import_Movie!E1:G100,3,FALSE) [assuming the new column gets inserted and becomes the column E]
PRO: Changes to the formula are easy and it is clear what is happening
CON: a new column will need to be created and maintained for all rows on the movie table (if this is an import, this may be difficult)

 

[mauricem72]

INDEX/MATCH works perfectly! Thank you!

Two methods:

First method:
Use an Array formula using INDEX/MATCH
=INDEX(Import_Movie!F1:F100,MATCH("0504",TEXT(Import_Movie!D1:D100,"MMDD"),0))
(CONFIRM THIS FORMULA BY PRESSING CTRL+SHIFT+ENTER)

PRO: No changes needed to the underlying data
CON: a bit more complicated formula and will need to always confirm with CTRL+SHIFT+ENTER


Second Method:
Add a 'helper' column to the Import Movie tab
Add a NEW column to the Import Movie tab which contains only the MONTH/DATE information
This new column should basically be =TEXT(D2,"MMDD")
The new VLOOKUP formula would then be =VLOOKUP("0504",Import_Movie!E1:G100,3,FALSE) [assuming the new column gets inserted and becomes the column E]
PRO: Changes to the formula are easy and it is clear what is happening
CON: a new column will need to be created and maintained for all rows on the movie table (if this is an import, this may be difficult)