Multiple IF formulas based on another cell

[Reggie Mcqueen]

Hi all,

I have a bunch of data for a delivery service, depending on the area the runs go out 3 or 4 times a day all at different times, I've done individual IF formulas based on there scheduled arrival times, but this data will change every week so i need to some how auto populate a different formula in each cell based on the delivery times they are due to go out, its for a delivery service that covers the whole country, so as you can imagine its a rather large file. is there a way I can set up a formula that if one cell = another cell then use a specific formula, but if another cell is for another area, use another formula?

I'm not sure that even makes sense.... any help would be appreciated

 

[bebo021999]

Try to create a sample sheet, input sample data, then guide us to walk through.

 

[Reggie Mcqueen]

Try to create a sample sheet, input sample data, then guide us to walk through.

So I need this to automatically put the correct formula in the "Uniref" box based on its "delivery area", this is so it splits the runs into run 1,2,3 etc and then gives a true reading as to whether the delivery was Hit or Missed.

 

[bebo021999]

Book1
	A	B	C	D	E
1				Schedule time
2	Lowestoff	1996PE30Uni1	10:15	10:30
3	Lowestoff	1996PE30Uni2	12:15	13:00
4	Lowestoff	1996PE30Uni3	14:15	17:00
5	Norwich	z55t9w34sfu 1		10:30
6	Norwich	z55t9w34sfu 2		11:30
7	Norwich	z55t9w34sfu 3		14:00
8	Norwich	z55t9w34sfu 4		17:00
9
10
11
12
13
14					Actual Arrival time
15	Lowestoff	1996PE30Uni	1996PE30Uni1		10:17
16	Lowestoff	1996PE30Uni	1996PE30Uni2		12:59
17	Lowestoff	1996PE30Uni	1996PE30Uni3		14:04
18	Norwich	z55t9w34sfu	z55t9w34sfu 4		14:59
Sheet2

Cell Formulas
Range	Formula
C15:C18	C15	=INDEX($B$2:$B$8,MATCH(1,INDEX(($A$2:$A$8=A15)*(D$2:$D$8>=E15),),0))

 

[Reggie Mcqueen]

Book1
	A	B	C	D	E
1				Schedule time
2	Lowestoff	1996PE30Uni1	10:15	10:30
3	Lowestoff	1996PE30Uni2	12:15	13:00
4	Lowestoff	1996PE30Uni3	14:15	17:00
5	Norwich	z55t9w34sfu 1		10:30
6	Norwich	z55t9w34sfu 2		11:30
7	Norwich	z55t9w34sfu 3		14:00
8	Norwich	z55t9w34sfu 4		17:00
9
10
11
12
13
14					Actual Arrival time
15	Lowestoff	1996PE30Uni	1996PE30Uni1		10:17
16	Lowestoff	1996PE30Uni	1996PE30Uni2		12:59
17	Lowestoff	1996PE30Uni	1996PE30Uni3		14:04
18	Norwich	z55t9w34sfu	z55t9w34sfu 4		14:59
Sheet2

Cell Formulas
Range	Formula
C15:C18	C15	=INDEX($B$2:$B$8,MATCH(1,INDEX(($A$2:$A$8=A15)*(D$2:$D$8>=E15),),0))

Fantastic, thank you!

 

[Reggie Mcqueen]

Book1
	A	B	C	D	E
1				Schedule time
2	Lowestoff	1996PE30Uni1	10:15	10:30
3	Lowestoff	1996PE30Uni2	12:15	13:00
4	Lowestoff	1996PE30Uni3	14:15	17:00
5	Norwich	z55t9w34sfu 1		10:30
6	Norwich	z55t9w34sfu 2		11:30
7	Norwich	z55t9w34sfu 3		14:00
8	Norwich	z55t9w34sfu 4		17:00
9
10
11
12
13
14					Actual Arrival time
15	Lowestoff	1996PE30Uni	1996PE30Uni1		10:17
16	Lowestoff	1996PE30Uni	1996PE30Uni2		12:59
17	Lowestoff	1996PE30Uni	1996PE30Uni3		14:04
18	Norwich	z55t9w34sfu	z55t9w34sfu 4		14:59
Sheet2

Cell Formulas
Range	Formula
C15:C18	C15	=INDEX($B$2:$B$8,MATCH(1,INDEX(($A$2:$A$8=A15)*(D$2:$D$8>=E15),),0))

Morning,

I don't suppose you know what would be the easiest way to now split that into days?

one of the runs goes out at different times on a Friday so I need to do it based on the day aswel