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 2942 users have said thank you to us this month

Subscribe To Our Newsletter!

The Best of CCM in Your Inbox

Subscribe To Our Newsletter!