Previous Topic Next Topic

If X is between A and B, then C

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

Réponse 1 / 1
tompols Posts 1273 Registration date Wednesday 28 July 2004 Status Contributor Last seen 25 November 2013 28
13 Jan 2010 à 06:32
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...