Weirdest problem ever - excel not matching matching text

I cant explain it really well other than showing an example in file, so I have attached it here.

Basically I downloaded a file with hourly data. I copy pasted it to a new excel document. The index key of the data is two time stamps with the word "to" in between. Like so: [2014-11-18 01:00:00*to 2014-11-18 02:00:00]

For the purpose of what I'm doing, I manually recreated a table with some identical index strings, so I would be able to use LOOKUP to find the value for any of the rows in the original data. Let's say I wanted the value for the time slot 2014-11-18 01:00:00*to 2014-11-18 02:00:00, I would use a lookup function in the table to get the value corresponding to that timeslot.

The only problem is, even if I type the PERFECT match of the index string into my lookup formula, it still comes out with an #N/A error.

It shouldnt be formatting, because the 'EXACT' formula ignores formatting, and still returns FALSE when I try to directly match the index key to my manually entered key. I'm thinking it might be something to do with ASCII/HTML character mismatch or something, but really have run out of ideas how to work it.

/A

OK this is even weirder, but after posting I saw that excelforum.com inserted a small * before the word 'to'. That star was not showing up in excel when I copied the data. If I try to enter a * symbol into the lookup formula EUREKA it works... PLEASE tell me whats going on here!

/A

I copied A2 from the formula bar and pasted it into A6. Your formula works fine. Those strings are not identical. Sometimes non-printable characters will show up in copied data. Try this array formula in E16 and fill down to E17.

Formula:

Please Login or Register  to view this content.

Array formulas are not committed in the regular way. You commit by pressing and holding Ctrl + Shift as you hit Enter. You will know that it has been entered successfully when you can see curly braces {} around your formula in the formula bar. You do not type these in yourself. Excel does it for you.

It counts character 160. These frequently show up when copying/pasting from tables on the internet. They are non-printable and are difficult to detect. E16 has one E17 does not.

Thanks Glenn....I failed to complete the thought......past my bedtime. LOL

Hi,

Please refer to Office.com off line help, which quotes as under:

"When searching text values in the first column of table_array, ensure that the data in the first column of table_array does not contain leading spaces, trailing spaces, inconsistent use of straight ( ' or " ) and curly ( ‘ or “) quotation marks, or nonprinting characters. In these cases, VLOOKUP might return an incorrect or unexpected value."

If you remove the spaces, vlookup function executes the result correctly.