vlookup time help. Urgent! Thanks!

[nkagar2]

Hello - I am trying the vlookup to categorize time into periods. However I am unable to format the lookup value time into hh:mm:ss. As a result I am getting #nA error. Please help me. This is time sensitive for me!!!

Main issue: When the data is imported, we receivetime as a text value (eg. 64539 instead of 06:45:39) also the period range entered are text values. When I convert both into hh:mm:ss it results #n/a error. However if I just type in the time 06:45:39 then it will retrieve the correct result. Please help.

Time	Range	Period
64856	070000	AM
	090000	AM-OFF
	110000	NOON
	130000	PM-OFF
	160000	PM

<colgroup><col width="64" span="4" style="width:48pt"> </colgroup><tbody>
</tbody>

=VLOOKUP(A2,B2:C6,2,TRUE) results in #n/A

 

[jbeaucaire]

You're getting N/A not because the formula is wrong, but because 64856 is below the lowest value in your table, 70000. Lower that number to an earlier value, or add a new line at the top of your table to cover the "out of bounds" time.

Excel 2010
	A	B	C	D	E	F
1	Time	Range	Period
2	64856	0	Too Early			Too Early
3		70000	AM
4		90000	AM-OFF
5		110000	NOON
6		130000	PM-OFF
7		160000	PM
Sheet1

Cell Formulas
Range	Formula
F2	=VLOOKUP(A2,B2:C7,2,TRUE)

 

[palzyp]

Hi
Using True in vlookup looks for an exact match and failing that it looks for the largest value that is less than the lookup value.
In this case it's looking for a value in the data range less than 64856, which you don't have, and so is throwing up an #N/A.
To remedy this you need to insert cells under Range and Period, as below, to represent times less than 07:00 and amend the vlookup data range as necessary to include the additional cells.

Time	Range	Period
64856	0	PM
	070000	AM
	090000	AM-OFF
	110000	NOON
	130000	PM-OFF
	160000	PM

<tbody>
</tbody>

Hope this helps!

EDIT - sorry, I didn't see the earlier reply whilst I was typing.

 

[nkagar2]

OH my goodness!!! I'm sooo embarrassed to have asked such a stupid question! Thank you all for your time and help. I appreciate it. Thanks again!

Hi
Using True in vlookup looks for an exact match and failing that it looks for the largest value that is less than the lookup value.
In this case it's looking for a value in the data range less than 64856, which you don't have, and so is throwing up an #N/A.
To remedy this you need to insert cells under Range and Period, as below, to represent times less than 07:00 and amend the vlookup data range as necessary to include the additional cells.

Time	Range	Period
64856	0	PM
	070000	AM
	090000	AM-OFF
	110000	NOON
	130000	PM-OFF
	160000	PM

<tbody>
</tbody>

Hope this helps!

EDIT - sorry, I didn't see the earlier reply whilst I was typing.

 

[mfexcel]

would you consider to convert your 6-digit number into a real time?