If X is between A and B, then C

Solved/Closed
Report
-
Posts
1219
Registration date
Wednesday July 28, 2004
Status
Contributor
Last seen
November 25, 2013
-
Hello,

Worksheet 1
Jan 10 Jan 12 Jan 12 10am
Jan 13 Jan 15 Jan 16 9am

Worksheet 2
Jan 11


I'm having trouble coming up with a formula to put in [Worksheet 2 Column B] that would come up with "Jan 12 10am" for Jan 11.

I tried =IF(AND(A1>='WORKSHEET 1'!A:A,A1<='WORKSHEET 1'!B:B),'WORKSHEET 1'!C:C,"")

but it doesn't work. Anybody know?

Thanks!

1 reply

Posts
1219
Registration date
Wednesday July 28, 2004
Status
Contributor
Last seen
November 25, 2013
28
Hi
you can use the follwing :
=INDEX(Feuil1!A2:B7;SOMMEPROD((Feuil1!A2:A7<=Feuil2!A1)*(Feuil1!B2:B7>=Feuil2!A1)*(LIGNE(Feuil1!A2:A7)-MIN(LIGNE(Feuil1!A2:A7)-1)));2) is in french...translated (not sure about translation of formulas) :
=INDEX(Feuil1!A2:B7;SUMPRODUCT((Feuil1!A2:A7<=Feuil2!A1)*(Feuil1!B2:B7>=Feuil2!A1)*(LINE(Feuil1!A2:A7)-MIN(LINE(Feuil1!A2:A7)-1)));2)
will post an example file if needed, excel will probably do the translation for us...
1
Thank you

A few words of thanks would be greatly appreciated. Add comment

CCM 2821 users have said thank you to us this month