how to cross check data based on time format

[legolas]

Dear All,

i got a task where i need to find who drove a vehicle on a certain time.
my given data is vehicle make, driver, and time range. so the issue is to compare time range VS fixed time
( attached file with details in H)

is it possible in H to have a formula or UDF so that : if F=B , and G between C&D , then copy A to H.

thanks for any help .



regards

[johnjohns]

Could not download your file. But this could be the formula for

if F=B , and G between C&D , then copy A to H.

IF(AND(F1=B1,G1>C1,G1<D1),A1,"")

[legolas]

Could not download your file. But this could be the formula for

IF(AND(F1=B1,G1>C1,G1<D1),A1,"")

I think i need an array formula

[legolas]

ok I uploaded my file.

[legolas]

is it not possible to compare a fixed time VS a time range

any help on this one please or i can just ignore


thanks again

[bebo021999]

Try this in H2:

=IF(G2="","",INDEX($A$1:$A$18,MAX(($B$2:$B$18=F2)*($C$2:$C$18<=G2)*($D$2:$D$18>=G2)*ROW($A$2:$A$18))))

Confirmed with Ctrl-Shift-Enter

[estige]

Maybe something like:
=INDEX($A$2:$A$18,SUMPRODUCT(($B$2:$B$18=F2)*($C$2:$C$18<=G2)*($D$2:$D$18>=G2),ROW($A$2:$A$18))-1,1)

[legolas]

Dear all

I am still can't get it to work so i uploaded part of my actual file

kindly take a look, thanks a lot

[estige]

Here is my suggestion.

[legolas]

@ OnErrorGoto0 & @estige

excatly what i need
thanks a lot