Lookup cell between dates

[kehong216]

Dear All,

I have a situation:

I need to manufacture something and I need to change battery regularly to ensure the manufacture does not fail.
I want column B to remind me which battery to use based on the information from cells E-G.

For example, on 10/10/2020 (A2), I need to use battery A because battery A is opened on 23/09/2020 and expires on 23/12/2020

My formula in B2 is =IF(AND(A2>$F$2,A2<$G$2),$E$2,OFFSET($E$2,1,0))

This works well, but when this formula cannot pick up battery C because the offset value is 1 row and not dynamic. Sorry I may not have explained very well.

Please find the attached Excel and I'd really appreciate if you could give me a hand on this.

Kind regards,
Ken

[Phuocam]

Try this in B2:

=LOOKUP(2,1/($F$2:$F$8<>"")/($F$2:$F$8<=A2)/($G$2:$G$8>=A2),$E$2:$E$8)

[kehong216]

Thanks a lot! This solves my problem.

Do you mind explaining how does this formula work?