0

I need to search a date and a value (name) and the return the first value of the first entry and past it to another worksheet, and then a second value on the last entry for another cell in the same worksheet.

As you can see in the images, I think is easier to understand.

Result:

Result.

Table to look:

Table to look.

0

This method solves the issue:

enter image description here

  • Array (CSE) formula in Cell D28:

    {=MIN(IF(($A$18:$A$25=$A28)*($B$18:$B$25=$B28),$D$18:$D$25))}
    
  • Array (CSE) formula in Cell E28:

    {=MAX(IF(($A$18:$A$25=$A28)*($B$18:$B$25=$B28),$E$18:$E$25))}
    

N.B.

  • Finish both formula with Ctrl+Shift+Enter.

Edited:

Below shown method is been included since OP is suppose to get Login & Logout time using multiple criteria (Date, Driver 1 & Driver 2) also.

enter image description here

  • An array (CSE) formula in D29:

    {=MIN(IF($A$18:$A$25=$A29,IF($B$18:$B$25=$B29,IF($C$18:$C$25=$C29,$D$18:$D$25))))}
    
  • An array (CSE) formula in E29:

    {=MAX(IF($A$18:$A$25=$A29, IF($B$18:$B$25=$B29,IF($C$18:$C$25=$C29,$E$18:$E$25))))}
    

N.B.

  • Finish formula with Ctrl+Shift+Enter.
  • Alter Date, Driver's name as needed.
  • Adjust cell references in the formula as needed.
|improve this answer|||||
  • Hi Rajesh, thank you a lot. It works but it works only in the first column (Driver 1). What I want is all the ocurrences of Driver 1 and Driver 2 columns. Can I extend the search? For example: {=MIN(IF(($A$18:$A$25=$A28)*($B$18:$C$25=$B28),$D$18:$D$25))} Thank you a lot for your help. Kind regards. – Focha Oct 27 '19 at 20:18
  • I've figured it out. I can put do it with: {=MIN(IF(($A$18:$A$25=$A28)*(OR($B$18:$B$25=$B28,$C$18:$C$25=$B28)),$D$18:$D$25))} Thank you very much. – Focha Oct 27 '19 at 20:40
  • I have a problem... when I change the name, the values do not update. – Focha Oct 27 '19 at 20:47
  • @Focha,, glad to help you,, what you are trying may not work sometimes since with Driver 1 & 2 the Date is a criteria also,, and login time works with MIN function so it will consider Date + Value in cell B28 any odd combination will return unexpected result or even error !! – Rajesh S Oct 28 '19 at 6:22
  • @Focha,, now check the post, I've shown how to use both Driver's name for Date to get Login and logout time. – Rajesh S Oct 28 '19 at 7:15

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.