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:

Origin	Destination
MADISON	LAS VEGAS
MADISON	CHICAGO
MADISON	ATLANTA
MADISON	BOSTON
CHICAGO	MADISON
CHICAGO	LITTLE ROCK
CHICAGO	PHOENIX
LITTLE ROCK	SEATTLE
LITTLE ROCK	CHICAGO

<TBODY>
</TBODY>

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:

Origin	Destination
MADISON	LAS VEGAS
MADISON	CHICAGO
MADISON	ATLANTA
MADISON	BOSTON
CHICAGO	MADISON
CHICAGO	LITTLE ROCK
CHICAGO	PHOENIX
LITTLE ROCK	SEATTLE
LITTLE ROCK	CHICAGO

<tbody>
</tbody>

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
1	Origin	Destination	*	Origin	Destination	Connecting Flights
2	MADISON	LAS VEGAS	*	MADISON	LITTLE ROCK	CHICAGO
3	MADISON	CHICAGO	*	MADISON	LITTLE ROCK	LAS VEGAS
4	MADISON	ATLANTA	*	*	*	*
5	MADISON	BOSTON	*	*	*	*
6	CHICAGO	MADISON	*	*	*	*
7	CHICAGO	LITTLE ROCK	*	*	*	*
8	CHICAGO	PHOENIX	*	*	*	*
9	LITTLE ROCK	SEATTLE	*	*	*	*
10	LAS VEGAS	LITTLE ROCK	*	*	*	*

<tbody>
</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.