Hi
I've got this formula which works correctly but is a bit long winded and has a few nested IF statements;
=IF(G7="eombh",INDEX(E2:E22,MATCH(1,(G2=A2:A22)*(G3=B2:B22),0)),IF(G7="eom",INDEX(D2:D22,MATCH(1,(G2=A2:A22)*(G3=B2:B22),0)),(INDEX(C2:C22,MATCH(1,(G2=A2:A22)*(G3=B2:B22),0)))))
G2= system
G3 = Day
G7="eombh etc"
[TABLE="width: 500"]
<tbody>[TR]
[TD]System A
[/TD]
[TD]Monday[/TD]
[TD]08:00[/TD]
[TD]08:15[/TD]
[TD]09:00[/TD]
[/TR]
[TR]
[TD]System A[/TD]
[TD]Tuesday[/TD]
[TD]08:30[/TD]
[TD]08:45[/TD]
[TD]09:15[/TD]
[/TR]
[TR]
[TD]System B[/TD]
[TD]Monday[/TD]
[TD]07:00[/TD]
[TD]07:15[/TD]
[TD]08:15[/TD]
[/TR]
[TR]
[TD]System B[/TD]
[TD]Tuesday[/TD]
[TD]07:30[/TD]
[TD]07:45[/TD]
[TD]08:50[/TD]
[/TR]
</tbody>[/TABLE]
can anyone help with simplify it?
many thanks
I've got this formula which works correctly but is a bit long winded and has a few nested IF statements;
=IF(G7="eombh",INDEX(E2:E22,MATCH(1,(G2=A2:A22)*(G3=B2:B22),0)),IF(G7="eom",INDEX(D2:D22,MATCH(1,(G2=A2:A22)*(G3=B2:B22),0)),(INDEX(C2:C22,MATCH(1,(G2=A2:A22)*(G3=B2:B22),0)))))
G2= system
G3 = Day
G7="eombh etc"
[TABLE="width: 500"]
<tbody>[TR]
[TD]System A
[/TD]
[TD]Monday[/TD]
[TD]08:00[/TD]
[TD]08:15[/TD]
[TD]09:00[/TD]
[/TR]
[TR]
[TD]System A[/TD]
[TD]Tuesday[/TD]
[TD]08:30[/TD]
[TD]08:45[/TD]
[TD]09:15[/TD]
[/TR]
[TR]
[TD]System B[/TD]
[TD]Monday[/TD]
[TD]07:00[/TD]
[TD]07:15[/TD]
[TD]08:15[/TD]
[/TR]
[TR]
[TD]System B[/TD]
[TD]Tuesday[/TD]
[TD]07:30[/TD]
[TD]07:45[/TD]
[TD]08:50[/TD]
[/TR]
</tbody>[/TABLE]
can anyone help with simplify it?
many thanks