how to calculate times based on two variables

[Przymorze]

Dear excel pros,

I'm having a problem with what formulas to use on attached sheet:
arrival times.xlsx

There are two tabs in my sheet. "calculation sheet" that shows names, arriving airport and flight arriving time.
Basically I need to count estimated arrival time of "name" based on other two variables with regards to people arriving at certain destination:
airport that person is are arriving into
time he/she boards the train to destination

...taken from tab "arrival times" where I will manually input data based on actual train timetables.

In real life I don't have to calculate 3 people but around 100 but i'm struggling so far with find a proper formula to make my life easier!

Your help is much appreciated.

Thank you in advance.

[alansidman]

Will there only be three location to manage? MUC, GVA and ZRH?

[Przymorze]

Will there only be three location to manage? MUC, GVA and ZRH?

Hi alansidman,

Thanks for looking into this post... Yes there will be only 3!

Regards,

[shg]

	A	B	C	D	E	F	G	H	I	J
1	Airport	Flt Arr	Train Dep	Train Arr		Who	Airport	Flt Arr	Train Arr
2	GVA	09:15	10:00	12:30		John	MUC	09:50	12:00	I2: {=INDEX($D$2:$D$12, MATCH(G2 & H2, $A$2:$A$12 & $B$2:$B$12, 0))}
3	GVA	09:35	10:00	12:30		Mike	MUC	12:20	15:00
4	GVA	12:50	13:30	16:00		Many	GVA	12:50	16:00
5	GVA	14:30	15:15	17:45		Lindsey	ZRH	11:35	15:00
6	GVA	15:40	16:15	18:45
7	ZRH	08:40	10:00	13:00
8	ZRH	11:35	12:00	15:00
9	ZRH	14:40	15:30	18:30
10	MUC	09:50	11:00	12:00
11	MUC	12:20	14:00	15:00
12	MUC	15:35	17:00	18:00

The curly braces means that the formula MUST be confirmed with Ctrl+Shift+Enter instead of just Enter. You can't enter the braces manually.

[alansidman]

An alternative solution if you wish to keep your spreadsheet as you currently have formatted.
In D2:
=IF(B2="MUC",VLOOKUP(C2,'Arrival Times'!$I$2:$K$4,3,FALSE),IF(B2="GVA",VLOOKUP(C2,'Arrival Times'!$A$2:$C$6,3,FALSE),VLOOKUP(C2,'Arrival Times'!$E$2:$G$4,3,FALSE))) and copy down.

[newdoverman]

I named the three ranges on the Arrival Times worksheet GVA, ZRH and MUC then used these names in a simple VLOOKUP.

Please Login or Register  to view this content.

The file should be self explanatory.