Sllight Issue with VLOOKUP

**jablo1312** · 06-19-2012, 12:48 PM

My formula is functioning fine, but the value I'm looking to copy is a time expressed as, for example, 1:11 (meant to represent 1 minutes, 11 seconds,etc) 0:49, etc. When I use the VLookup formula to transfer the times, 1:11 comes up as .049306, 0:43 as .034027, etc. Any idea why this would be happening/what I coudl do to solve it? Thanks.

**dilipandey** · 06-19-2012, 12:51 PM

Hi jablo1312,

You need to format the answer cell as mm:ss or you can include the Text function in your formula as =TEXT(<your formula>,"mm:ss")... in case of any issues, upload the sample workbook.. thanks.

Regards,
DILIPandey

<click on below 'star' if this helps>

**Cutter** · 06-19-2012, 12:52 PM

That's just a formatting issue.
Format the cell with the formula in it the same as the cell with the 1:11 in it.

**SteveG** · 06-19-2012, 12:58 PM

Hi,
There are 2 issues. Excel will interpret 1:11 (General formatting) as 1 hour 11 minutes not 1 minute 11 seconds. To change that, divide the lookup by 60 (=VLOOKUP(A1,C1:B10,2,FALSE)/60)). The second issue is the formatting of the cell you are returning the value to. Change that to m:ss.

HTH
Steve