Ah, I see. 4.16875 is actually the correct value, Aikeedoh; it's just not formatted the way you want.



Let's say that the worksheet where you're doing the VLOOKUP is named Master, and the worksheet it's looking at is Target. In Target it looks right ("100:03:00"), but in Master it's wrong ("4.16875")—or rather the value is right, but the format is wrong. In Master, change the formatting to "[h]:mm:ss", and 4.16875 will be displayed as "100:03:00".

Here's why: Excel stores dates and times as whole numbers. 4 is four days; 4.5 is four and a half days, ie 4 days and 12 hours. Just now the date and time where I am is 41507.66646, that is, 41507 days since 1900-01-01, plus .66646 of another day. That comes out to 2013-08-21 15:59:42.550, you see.

Now, what Excel is finding is 4.16875. If you tell Excel to format the result (in Master) as "yyyy-mm-dd", it'll ignore the time part and just display "1900-01-04" (four days since the start of 1900). If you format it as "hh:mm:ss", it'll ignore the whole days and show just the fraction of the day as "04:03:00". But put square brackets around the hours, "[h]:mm:ss", and it'll show it the way you want.

Your problem is that Excel converts anything that vaguely looks like a date INTO a date. Simply change the number format of this column to Text and you should be OK. Your value is not really a number, it is text composed of alphanumeric characters which happen to be mostly digits - but this is not the sort of number used in calculations.