Replace Dates with Text in a formula

[brooksc29]

Can someone please tell me how to run a Vlookup that will find a date and if that date falls in between certain dates, return text based on where the date falls?

more specific...

cell M6 on PMS sheet runs a VLookup to cell K2 on PPS sheet and sees the date 1/18/2010.

the following table on sheet SD in in cells C3:D14
10/15/2006:12/20/2006 Pre T 10/15/2007:12/20/2007 Pre T 10/15/2008:12/12/2008 Pre T 10/15/2009:12/12/2009 Pre T 12/21/2006:1/16/2007 T to F 12/21/2007:1/18/2008 T to F 12/21/2008:1/20/2009 T to F 12/21/2009:1/15/2010 T to F 1/17/2007:4/9/4007 F to H 1/19/2008:4/9/2008 F to H 1/21/2009:4/9/2009 F to H 1/16/2010:4/9/2010 F to H

since 1/18/2010 falls between 1/16/2010:4/9/2010, i need to have F to H return in cell M6 on the PMS sheet

[mdbct13]

Please post a sample workbook with a before and and expected results. Please don't expect people to recreate what you already have done. To attach the file scroll down to the Manage Attachments button.

[brooksc29]

i will post some snapshots

[brooksc29]

need a few more posts to be able to

[brooksc29]

will post snapshots, i need 5 total posts...

[brooksc29]

one more

[brooksc29]

here goes, maybe this will help.



this is the date ranges
column C is DateDateRange, D is DateDateRange2, and E is TextRange
http://i1015.photobucket.com/albums/af273/brooksc29/DDR.jpg?t=1281571971

this is the original cell with code
http://i1015.photobucket.com/albums/af273/brooksc29/ad.jpg?t=1281572202

and this is where the date to lookup is found
http://i1015.photobucket.com/albums/af273/brooksc29/db.jpg?t=1281572353

what needs to change to make this work?

[mbarron]

The photos don't help. Please post an example workbook with an explanation of what you are trying to achieve.

[brooksc29]

my workbook exceeds the size limit to post.

do the pictures not help at all b/c you can't see them? b/c i can see them.

here's a description:
cell M6 on Pitchers Master sheet runs a VLookup to find the name in cell B6 of that sheet and match it with the same name in PITCHERSplatformSHEET and return cell 11 (which is a date). Then I need to compare that date to the table on statlist dates sheet and see where the date falls between. Finally when it locates the correct range of dates, return the text in column D corresponding to the date range.

the following table on sheet statlist dates in cells C3:D14. Dates in cells C3:C14 is named DateDateRange. Cells in D3:D14 are TextRange.

10/15/2006:12/20/2006 Pre T 10/15/2007:12/20/2007 Pre T 10/15/2008:12/12/2008 Pre T 10/15/2009:12/12/2009 Pre T 12/21/2006:1/16/2007 T to F 12/21/2007:1/18/2008 T to F 12/21/2008:1/20/2009 T to F 12/21/2009:1/15/2010 T to F 1/17/2007:4/9/4007 F to H 1/19/2008:4/9/2008 F to H 1/21/2009:4/9/2009 F to H 1/16/2010:4/9/2010 F to H

[brooksc29]

not sure if this will help at all, but this is the current formula I am using, which is returning #N/A

{=INDEX(TextRange,MATCH(1,(DATEVALUE(LEFT(DateDateRange,FIND(":",DateDateRange)-1))<=VLOOKUP(B6,PITCHERSplatSTATS,11,FALSE)*DATEVALUE(MID(DateDateRange,FIND(":",DateDateRange)+1,20))>=VLOOKUP(B6,PITCHERSplatSTATS,11,FALSE))))}

let me preface this by saying thank you in advance for your help

[mbarron]

I can see the pictures, but they are of little help. I want to help you, but I am not going to try and recreate your data.

You don't need to post the actual workbook. Create a workbook with enough data to demonstrate what you want to do.

[brooksc29]

this may be ignorant, but i'm not sure what else you need to know other than those pictures and the formula above.

I don't need you to recreate any data, just tell me if you see anything wrong with what that formula is saying and what I'm trying to do

[brooksc29]

maybe this book will help?

in cell b1 on vlookup sheet, find the date on the date worksheet corresponding to the name in call a1 on the vlookup sheet. then see where that date falls between on the date range sheet and return the text next to that date range.

[mbarron]

Using your workbook with the following named ranges

txt = the column whose values are to be returned
dtRange = the column containing the date ranges
vlTable = the table used to return the date


{=INDEX(txt,MATCH(1,(VLOOKUP(A2,vlTable,2,FALSE)>=DATEVALUE(LEFT(dtRange,FIND(":",dtRange)-1)))*(VLOOKUP(A2,vlTable,2,FALSE)<=DATEVALUE(MID(dtRange,FIND(":",dtRange)+1,99))),0))}

the Vlookup >= or <= DateValue sections needed to be enclosed in ()'s. I would also include the 0 argument (exact match) for the Match function

[brooksc29]

i really really appreciate your help, but I'm still getting N/A. This is what I typed in for code... it deducted a few )) at the end of the formula after I hit CTR+SHFT+ENTER

{=INDEX(TextRange,MATCH(1,(VLOOKUP(B6,PITCHERSplatSTATS,11,FALSE)>=DATEVALUE(LEFT
(DateDateRange,FIND(":",DateDateRange)-1)))*VLOOKUP(B6,
PITCHERSplatSTATS,11,FALSE)<=DATEVALUE(MID(DateDateRange,FIND(":",DateDateRange)+1,99))),0)}

TextRange = the column whose values are to be returned
DateDateRange = the column containing the date ranges
PITCHERSplatSTATS = the table used to return the date

am I doing something wrong?

any way I could send you the actual workbook privately?

[p45cal]

Cross post:http://www.mrexcel.com/forum/showthread.php?p=2407396&posted=1#post2407396
brooksc29, if you must cross-post, at least tell others where you have done so. Have you posted anywhere else on this subject?

[mbarron]

The ,0 was in the wrong location. It was being used as the last argument in the INDEX function instead of the MATCH function. Also a set of missing was parenthesis around the second VLOOKUP comparison.

{=INDEX(TextRange,MATCH(1,(VLOOKUP(B6,PITCHERSplatSTATS,11,FALSE)>=
DATEVALUE(LEFT(DateDateRange,FIND(":",DateDateRange)-1)))*
(VLOOKUP(B6,PITCHERSplatSTATS,11,FALSE)<=
DATEVALUE(MID(DateDateRange,FIND(":",DateDateRange)+1,99))),0))}

[p45cal]

this may be ignorant, but… [snip] …I don't need you to recreate any data, just tell me if you see anything wrong with what that formula is saying and what I'm trying to do
On inspection there's not anything wrong with the formula. And that's the end of it.









Oh, wait a minute, that's not very helpful is it? So what's the next step?
Trying it out on a sheet perhaps.. but what might I need to do that?
Perhaps something like your data, something I can use to do some deeper probing into parts of the formula, which would involve… recreating your data.


I would offer you to send me the file, but I'm not prepared to change my locale settings (even temporarily - from previous experience this can cause problems) so I can't test.
I tested the formula I supplied at MrExcel, but had to adjust your strings to my locale (d/m/y).

[brooksc29]

OK, it was a "cross post". As I'm new to this forum stuff, I didn't realize people would be sensitive about that. It's a tough problem that no one's been able to figure out.

The code, even with moving the ",0" (which you didn't in your correction) still isn't working...

p45cal - I posted up a sample book a few posts back if you want to see data.

[brooksc29]

I tried the above code in the small workbook I posted here, and am still getting #N/A

[mbarron]

The code, even with moving the ",0" (which you didn't in your correction) still isn't working...

The ,0 was moved in my formula. In yours, it is the last argument in the INDEX function. In mine, it is the last argument in the MATCH. There are two )'s after it in mine while only one exists after in yours.

As far as the #N/A - did you confirm the formula as an array? Ctrl+Shift+Enter instead of just Enter. I've attached your book with the formula I posted.