Formula Locating Common Value

[ReaperX]

Hello,

I have a table with two columns of airports. Column 1 is the originating, and column 2 is the destination. Each airport is listed multiple times in each column. I have a formula that can find if a direct flight is possible, but I am looking for a formula that can find connections. As an example, I know Madison flies to Chicago, and I know Chicago flies to Little Rock. How can I find that Chicago is the connecting flight between Madison and Little Rock? I know some of these will have multiple connections, which I can try and figure out, but at least the first possible one should populate.

My data is set up as follows:
[TABLE="class: grid, width: 500"]
<TBODY>[TR]
[TD]Origin[/TD]
[TD]Destination[/TD]
[/TR]
[TR]
[TD]MADISON[/TD]
[TD]LAS VEGAS[/TD]
[/TR]
[TR]
[TD]MADISON[/TD]
[TD]CHICAGO[/TD]
[/TR]
[TR]
[TD]MADISON[/TD]
[TD]ATLANTA[/TD]
[/TR]
[TR]
[TD]MADISON[/TD]
[TD]BOSTON[/TD]
[/TR]
[TR]
[TD]CHICAGO[/TD]
[TD]MADISON[/TD]
[/TR]
[TR]
[TD]CHICAGO[/TD]
[TD]LITTLE ROCK[/TD]
[/TR]
[TR]
[TD]CHICAGO[/TD]
[TD]PHOENIX[/TD]
[/TR]
[TR]
[TD]LITTLE ROCK[/TD]
[TD]SEATTLE[/TD]
[/TR]
[TR]
[TD]LITTLE ROCK[/TD]
[TD]CHICAGO[/TD]
[/TR]
</TBODY>[/TABLE]

Any help is appreciated.

 

[Robert Mika]

Hello,

I have a table with two columns of airports. Column 1 is the originating, and column 2 is the destination. Each airport is listed multiple times in each column. I have a formula that can find if a direct flight is possible, but I am looking for a formula that can find connections. As an example, I know Madison flies to Chicago, and I know Chicago flies to Little Rock. How can I find that Chicago is the connecting flight between Madison and Little Rock? I know some of these will have multiple connections, which I can try and figure out, but at least the first possible one should populate.

My data is set up as follows:
[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD]Origin[/TD]
[TD]Destination[/TD]
[/TR]
[TR]
[TD]MADISON[/TD]
[TD]LAS VEGAS[/TD]
[/TR]
[TR]
[TD]MADISON[/TD]
[TD]CHICAGO[/TD]
[/TR]
[TR]
[TD]MADISON[/TD]
[TD]ATLANTA[/TD]
[/TR]
[TR]
[TD]MADISON[/TD]
[TD]BOSTON[/TD]
[/TR]
[TR]
[TD]CHICAGO[/TD]
[TD]MADISON[/TD]
[/TR]
[TR]
[TD]CHICAGO[/TD]
[TD]LITTLE ROCK[/TD]
[/TR]
[TR]
[TD]CHICAGO[/TD]
[TD]PHOENIX[/TD]
[/TR]
[TR]
[TD]LITTLE ROCK[/TD]
[TD]SEATTLE[/TD]
[/TR]
[TR]
[TD]LITTLE ROCK[/TD]
[TD]CHICAGO[/TD]
[/TR]
</tbody>[/TABLE]

Any help is appreciated.

Is this a finite list of desitantion or you have more and more...
What answer do you expect for each of the line?

 

[ReaperX]

The list is much longer and could change. The results are not part of that table at all. I have two dropdowns that allow me to choose departure airport and destination airport. When they don't have direct flights, I would like a cell to return a common flight. All of the flights in the list are reversible (i.e. Little Rock to Seattle is also Seattle to Little Rock). If it can populate a list of ALL common flights, that would be great too, but I really just need the first one that comes up.

 

[Matty]

Hi,

If I follow, try this...

Set up:

*	A	B	C	D	E	F
Origin	Destination	*	Origin	Destination	Connecting Flights
MADISON	LAS VEGAS	*	MADISON	LITTLE ROCK	CHICAGO
MADISON	CHICAGO	*	MADISON	LITTLE ROCK	LAS VEGAS
MADISON	ATLANTA	*	*	*	*
MADISON	BOSTON	*	*	*	*
CHICAGO	MADISON	*	*	*	*
CHICAGO	LITTLE ROCK	*	*	*	*
CHICAGO	PHOENIX	*	*	*	*
LITTLE ROCK	SEATTLE	*	*	*	*
LAS VEGAS	LITTLE ROCK	*	*	*	*

<tbody>
[TD="bgcolor: #cacaca, align: center"]1[/TD]

[TD="bgcolor: #cacaca, align: center"]2[/TD]

[TD="bgcolor: #cacaca, align: center"]3[/TD]

[TD="bgcolor: #cacaca, align: center"]4[/TD]

[TD="bgcolor: #cacaca, align: center"]5[/TD]

[TD="bgcolor: #cacaca, align: center"]6[/TD]

[TD="bgcolor: #cacaca, align: center"]7[/TD]

[TD="bgcolor: #cacaca, align: center"]8[/TD]

[TD="bgcolor: #cacaca, align: center"]9[/TD]

[TD="bgcolor: #cacaca, align: center"]10[/TD]

</tbody>

Array formula in F2 is:

=INDEX(B$2:B$10,MATCH(1,IF(A$2:A$10=D2,IF(B$2:B$10=INDEX(A$2:A$10,SMALL(IF(B$2:B$10=E2,ROW(B$2:B$10)-ROW(B$2)+1),ROWS(F$2:F2))),1)),0))

Which requires entry with CTRL+SHIFT+ENTER and can then be copied down...

Does it do as desired?

Matty

 

[ReaperX]

That is exactly what I was looking for and it works great. Thank you for your help.