VLOOKUP Multiple Criteria (Dates)

[NewExcelUser]

Hello - In column I, there is a list of dates - month and day only

In column J there is a list of dates, month,day, and year.

--I----------------J
1/14----------1/14/2009
1/14----------1/15/2009
1/14----------1/16/2009
1/15----------1/17/2009


in column K, I am trying to setup a Vlookup statement that states if the month and day in column I = the month and day in column J then return the value in column J.

I have the following formula, but it just returns N/A.

Please Login or Register  to view this content.

I have tried different variations and different methods to no avail. I have even tried adding an if statement into the mix. I have tried using direct/match without success and my knowledge of Excel in this area is limited.
Help is much appreciated. Thank you for your time.

[NBVC]

Are the values in column I dates formatted as m/dd or are they text entries (i.e "1/14")?

[NewExcelUser]

I'm not sure... The information is a result of a formula that pulls the data from another sheet. It is formatted as "General" when I go to "format cells" in the drop down.

I can tell you that if I place the following formulas in it comes up true...=IF(MONTH(H1)=MONTH(I1),"TRUE","FALSE"

Please let me know if there is anymore information that you need... thank you.

[NBVC]

Try perhaps?

=INDEX($K$1:$K$4,MATCH(MONTH(I1)&DAY(I1),MONTH($J$1:$J$4)&DAY($J$1:$J$4),0))

or

=INDEX($K$1:$K$4,MATCH(TEXT(I1,"mm/dd"),TEXT($J$1:$J$4,"mm/dd"),0))

where your table is in I1:K4... adjust as necessary.

After you adjust, you must confirm this formula with CTRL+SHIFT+ENTER not just ENTER.. then you will see { } brackets appear around the formula.

Then copy it down.

[NewExcelUser]

I have adjusted the formula to

Please Login or Register  to view this content.

and my result is as follows.

--I----------------J--------------------K
1/14----------1/14/2009----------1/14
1/14----------1/15/2009----------1/14
1/15----------1/16/2009----------1/14
1/15----------1/17/2009----------1/14
1/16----------1/18/2009----------1/15
1/16----------1/19/2009----------1/15

As is shown the results above, the numbers do not quite match up and only change after every fourth cell. Also, the year needs to appear in column K if possible.

Your time is appreciated.

[NBVC]

Maybe you meant:

Please Login or Register  to view this content.

REMEMBER: confirmed with CTRL+SHIFT+ENTER..

formatted to date format.

[NewExcelUser]

Wow! That works! Thank you soooo much! I would've never figured that out on my own. You're awesome. Thanks again.