Range Lookup

[keiserj]

Currently have a tab that is called MYYEAR which includes the following table.

MY10	7/1/2009​	6/30/2010​
MY11	7/1/2010​	6/30/2011​
MY12	7/1/2011​	6/30/2012​
MY13	7/1/2012​	6/30/2013​
MY14	7/1/2013​	6/30/2014​
MY15	7/1/2014​	6/30/2015​
MY16	7/1/2015​	6/30/2016​
MY17	7/1/2016​	6/30/2017​
MY18	7/1/2017​	6/30/2018​
MY19	7/1/2018​	6/30/2019​
MY20	7/1/2019​	6/30/2020​

Then a first tab that has data that I would like to look at the dates in column "D" and return in column E the corresponding model year. Currently using the following formula which I can get to work in same tab has MYYEAR but not when reference second tab.

=LOOKUP(2,1/(MYYEAR!$C$3:$C$13<=B2)/(MYYEAR!$D$3:$D$13>=B2),MYYEAR!$B$3:$B$13)

I have checked cell formats to be dates and even moved to same tab but getting NA.

Not sure what I'm doing wrong.

Thanks!

 

[jasonb75]

I have checked cell formats to be dates and even moved to same tab but getting NA.

Are they actual dates though?

A text string formatted as date is still a text string. If you change the format to number do you still see dates, or do you see numbers in the range of 40000 to 45000 (approx)?

 

[keiserj]

Are they actual dates though?

A text string formatted as date is still a text string. If you change the format to number do you still see dates, or do you see numbers in the range of 40000 to 45000 (approx)?

thanks for you information I found that the format from the export is not coming across correctly. If i edit each cell then my formula works.

12/30/2010​	MY11
1/14/2011​	MY11
12/29/2010	MY20

Any way to change this in the mass vs. each cell have 8000+ rows

 

[jasonb75]

Try selecting the column, format as date or general, then use text to columns to make them valid.

Data tab > Text to columns > Next > Uncheck all boxes > Next > Select your correct date format from the dropdown > Finish.

This should work as long as there are no hidden characters which you can sometimes get with imported data.

 

[keiserj]

Try selecting the column, format as date or general, then use text to columns to make them valid.

Data tab > Text to columns > Next > Uncheck all boxes > Next > Select your correct date format from the dropdown > Finish.

This should work as long as there are no hidden characters which you can sometimes get with imported data.

Thanks! Thats just what I needed!