TIME VLOOKUP returning #N/A value

[redspanna]

Hi all

I'm having trouble getting a simple VLOOKUP to return a time value

Simple data is shown below

Overtime.xlsx
	B	C	D	E	F	G	H	I
1	05:58	09:00	03:02	#N/A			03:00	00:00
2							03:01	00:15
3							03:02	00:30
4							03:03	00:45
5							03:04	01:00
6
Sheet1

Cell Formulas
Range	Formula
D1	D1	=C1-B1
E1	E1	=VLOOKUP(D1,H1:I5,2,FALSE)

so D1 is the time value between C1 and B1, I simple want the Value of D1 (03:02 in this example) to be searched in the table
E1 should bring back the time value of 00:30 but I'm getting the #N/A error

any help appreciated, assume its how I have the data stored but can't figure out what to do
]
Thanks

 

[Marius44]

Hello
I think it is better to enter TRUE and not FALSE

It's to say =VLOOKUP(D1,H!:I5,2,TRUE)

Hello,
Mario

 

[redspanna]

Hello
I think it is better to enter TRUE and not FALSE

It's to say =VLOOKUP(D1,H!:I5,2,TRUE)

Hello,
Mario

By using TRUE this doesn't give an exact match in the table...using TRUE in this example wil retuen the value 00:15 from the VLOOKUP whereas it should return 00:30

 

[Marius44]

Hello
You're right!
Add a column between H and I in the table with this formula
= ROUND (G1; 5)
In D1 = ROUND (C1-B1; 5)
in E1
= VLOOKUP (D1, H1: I5,2, FALSE)
Sorry for the wrong suggestion earlier.
Hello,
Mario

 

[redspanna]

Hello
You're right!
Add a column between H and I in the table with this formula
= ROUND (G1; 5)
In D1 = ROUND (C1-B1; 5)
in E1
= VLOOKUP (D1, H1: I5,2, FALSE)
Sorry for the wrong suggestion earlier.
Hello,
Mario

View attachment 63099

Had to use =ROUND(G1,5) and not =ROUND(G1;5) but think thats now working as desired

Many thanks @Marius44 , appreciate your help

 

[Marius44]

Hello
Glad I was helpful

Hello,
Mario