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>[TABLE="width: 0"]
<colgroup><col style="width: 350px"><col width="125"></colgroup><tbody>[TR]
[TD]Alien vs. Predator (2004)[/TD]
[TD="align: right"]August 13, 2004[/TD]
[/TR]
[TR]
[TD]Star Wars: Episode III - Revenge of the Sith (2005)[/TD]
[TD="align: right"]May 19, 2005[/TD]
[/TR]
[TR]
[TD]Superman Returns (2006)[/TD]
[TD="align: right"]June 28, 2006[/TD]
[/TR]
[TR]
[TD]Spider-Man 3 (2007)[/TD]
[TD="align: right"]May 4, 2007[/TD]
[/TR]
[TR]
[TD]The Invasion (2007)[/TD]
[TD="align: right"]August 17, 2007[/TD]
[/TR]
[TR]
[TD]I Am Legend (2007)[/TD]
[TD="align: right"]14 December 2007[/TD]
[/TR]
[TR]
[TD]Aliens vs. Predator: Requiem (2007)[/TD]
[TD="align: right"]December 25, 2007[/TD]
[/TR]
</tbody>[/TABLE]


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)