Obscure Index/Match/formatting problem

[Ride The Lightning]

Hi

I have the following formula:
=INDEX('bet log 27.8'!$E$2:$E$1000,MATCH(A2,'bet log 27.8'!$B$2:$B$1000,0))

The data for 'bet log 27.8'!$E$2:$E$1000 is made up of "lay" and "back", the data for 'bet log 27.8'!$B$2:$B$1000 is a time in the following format: hh:mm:ss. The Value in A2 is also a time in the same format: hh:mm:ss. The answer I am getting is N/A. The formatting is the same and the formula is correct as I have already tested it on other sheets. The wierd thing is when I copy and paste the relevent time from 'bet log 27.8'!$B$2:$B$1000 to A2 the formula returns the correct answer! I would have thought this meant a formatting issue but they are the same! I am stumped as to why this is happening, any answers much appreciated.

Regards

RTL

 

[Aladin Akyurek]

Hi

I have the following formula:
=INDEX('bet log 27.8'!$E$2:$E$1000,MATCH(A2,'bet log 27.8'!$B$2:$B$1000,0))

The data for 'bet log 27.8'!$E$2:$E$1000 is made up of "lay" and "back", the data for 'bet log 27.8'!$B$2:$B$1000 is a time in the following format: hh:mm:ss. The Value in A2 is also a time in the same format: hh:mm:ss. The answer I am getting is N/A. The formatting is the same and the formula is correct as I have already tested it on other sheets. The wierd thing is when I copy and paste the relevent time from 'bet log 27.8'!$B$2:$B$1000 to A2 the formula returns the correct answer! I would have thought this meant a formatting issue but they are the same! I am stumped as to why this is happening, any answers much appreciated.

Regards

RTL

What do you get with

=ISNUMBER(A2)

if this is entered say in G2?

 

[Ride The Lightning]

I get TRUE if I do that.

 

[Ride The Lightning]

The data I am getting the correct result with is pasted from a software application I use into one cell then I use the "Text to Columns" button on the data tab and split the data by space. This splits the data adequately enough to do an analysis. The other data data is taken from another software application I use that records data into excel CSV mode, then I convert it into a normal excel workbook to use inconjunction with other spreadsheets.

A little background info for this bizzare problem!

 

[Aladin Akyurek]

The data I am getting the correct result with is pasted from a software application I use into one cell then I use the "Text to Columns" button on the data tab and split the data by space. This splits the data adequately enough to do an analysis. The other data data is taken from another software application I use that records data into excel CSV mode, then I convert it into a normal excel workbook to use inconjunction with other spreadsheets.

A little background info for this bizzare problem!

Does this...

=INDEX('bet log 27.8'!$E$2:$E$1000,MATCH(A2&"",'bet log 27.8'!$B$2:$B$1000,0))

work?

 

[Ride The Lightning]

No, still getting N/A...

 

[Aladin Akyurek]

No, still getting N/A...

And...

=INDEX('bet log 27.8'!$E$2:$E$1000,MATCH("*"&A2&"*",'bet log 27.8'!$B$2:$B$1000,0))

 

[Ride The Lightning]

Still getting N/A

 

[Ride The Lightning]

Figured why it is not working.

For example, when I have the values "13:29:38" in A2 and "13:29:38" in the column 'bet log 27.8'!$B$2:$B$1000, when I change the format to General the value in A2 is "0.562246366" and the particular cell in 'bet log 27.8'!$B$2:$B$1000 is "0.56224537" so it is a rounding issue. How would I go about ensuring the rounding is adjusted so the values match up?

 

[Ride The Lightning]

Manged to totally fix it using MROUND, thanks to everyone who posted, appreciated.