Previous Topic Next Topic

If X is between A and B, then C

Solved/Closed
stay - Jan 12, 2010 at 04:14 PM
tompols Posts 1273 Registration date Wednesday July 28, 2004 Status Contributor Last seen November 25, 2013 - Jan 13, 2010 at 06:32 AM
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 response

Answer 1 / 1
tompols Posts 1273 Registration date Wednesday July 28, 2004 Status Contributor Last seen November 25, 2013 28
Jan 13, 2010 at 06:32 AM
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