Value is coming back as a date when using VLOOKUP

[TexasHeat]

If you look below, I have a list on the far right with dates that I'm trying to match to the date on the far left using VLOOKUP. It appears that the argument function I'm using is functioning properly as you can see #N/A is being returned when the date is not correlated to the list on the right. However, the value is coming back as the value trying to be obtained from the far right; i.e. 19, and is instead coming back as what looks like a date format, "42010". (The values look to be one below,1942010, but just won't align correctly.) I'm assuming its auto formatting to date because this happens so often, but it's weird since VLOOKUP I thought gives a direct value based on the position in the chart you tell it to look. Any ideas?


[TABLE="width: 612"]
<colgroup><col><col><col><col><col><col></colgroup><tbody>[TR]
[TD]12497290[/TD]
[TD]Days Between Fueling[/TD]
[TD][/TD]
[TD][/TD]
[TD]12497290[/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]12497290 PU 1/2 T 2X4 CRW CAB[/TD]
[TD="align: right"]12/18/2014[/TD]
[TD][/TD]
[TD][/TD]
[TD] 01/06/2015[/TD]
[TD="align: right"]19[/TD]
[/TR]
[TR]
[TD] 01/06/2015[/TD]
[TD="align: right"]19[/TD]
[TD]42010[/TD]
[TD][/TD]
[TD] 01/13/2015[/TD]
[TD="align: right"]7[/TD]
[/TR]
[TR]
[TD]Lance Songy[/TD]
[TD="align: center"]#VALUE![/TD]
[TD="align: center"]#N/A[/TD]
[TD][/TD]
[TD] 03/02/2015[/TD]
[TD="align: right"]48[/TD]
[/TR]
[TR]
[TD]419543[/TD]
[TD="align: center"]#VALUE![/TD]
[TD="align: center"]#N/A[/TD]
[TD][/TD]
[TD] 03/24/2015[/TD]
[TD="align: right"]22[/TD]
[/TR]
</tbody>[/TABLE]
[TABLE="width: 500"]
<tbody></tbody>[/TABLE]

 

[ghrain22]

you're sample data didn't quite come across formatted correctly, so it's a bit confusing.

My understanding is that your date is getting formatted as a number. As 42010 is 01/06/2015 formatted as a number. You can simply reformat the cellsin the home tab. Or you can use the text function to reformat it.

ie.
Text(vlookup(....),"mm/dd/yyyy")

 

[TexasHeat]

Yes and no. So I'm using the date in the far left column to do a vlookup in the list of dates in the right column. I'm then telling vlookup to pull the number 1 column to the right of the date in the FAR right column in order to have that number listed in accordance to the date on the far left column. Since the list isn't distributed proportionally and the vlookup is being applied to values that aren't dates it shoots back a value of #N/A. Which is perfect to apply the vlookup format to over 14,000 cells because I'll be able to go back with a Find All and delete the formatting for the ones that say #N/A. However, I'm getting back the result depicted as 42010 for date 01/06/2015 instead of the value that should be there in accordance with the right column which is 19.

I just tried to reformat the entire table to text, but it ended up changing all the dates into values that look similar to the formatting of 42010.

I think my formula may have been wrong from the get go with it being =VLOOKUP(A8,E7:E7228,1,FALSE). Since I designated in the formula for it to go to the 1 column it was pulling dates instead of the number listed to the right of dates. So I've gone back and changed it, but now it is giving me a #REF error. Now I'm really confused. Any ideas

 

[TexasHeat]

I'm an idiot. I got it figured out. The range I was selecting didn't match my vlookup search correctly. I rewrote the formula and now it's working great. However, now the data isn't pulling what is need because I have the topics and subtopics. I need to find a better way to pull it out.