Relating two lists-Index? Lookup? Match?, Vlookup?

[Marc Enzi]

I have several very long lists with data that I am trying to use to create a comprehensive list.

Airports have both a 3 letter IATA code (International Airline Transport Association) they also have a 4 letter ICAO code (International Civil Aeronautics Association). So Chicago O'Hare has a 3 letter IATA Code ORD and an ICAO COde of KORD, Mexico City is MEX (IATA) and MMMX (ICAO).

I have one partial list (Sheet 1) of both codes, and another list (SHeet 2)of ICAO codes (but no IATA codes)

I want to take the sheet 1 and use it to populate the IATA code column on sheet 2.

Sheet 1
IATA ICAO Name
AAA....NTGA...French Polynesia
ABE....KABE....Allentown PA
MEX....MMMX...Mexico City
ORD...KORD...Chicago IL

Sheet 2
IATA....ICAO....Name
...........KABE....Allentown PA
...........KORD...Chicago IL
...........MMMX...Mexico City
...........NTGA....French Polynesia

Thanks I appreciate it.

 

[Aladin Akyurek]

Re: Relating two lists

I have several very long lists with data that I am trying to use to create a comprehensive list.

Airports have both a 3 letter IATA code (International Airline Transport Association) they also have a 4 letter ICAO code (International Civil Aeronautics Association). So Chicago O'Hare has a 3 letter IATA Code ORD and an ICAO COde of KORD, Mexico City is MEX (IATA) and MMMX (ICAO).

I have one partial list (Sheet 1) of both codes, and another list (SHeet 2)of ICAO codes (but no IATA codes)

I want to take the sheet 1 and use it to populate the IATA code column on sheet 2.

Sheet 1
IATA ICAO Name
AAA....NTGA...French Polynesia
ABE....KABE....Allentown PA
MEX....MMMX...Mexico City
ORD...KORD...Chicago IL

Sheet 2
IATA....ICAO....Name
...........KABE....Allentown PA
...........KORD...Chicago IL
...........MMMX...Mexico City
...........NTGA....French Polynesia

Thanks I appreciate it.

Sheet1(source)

Book3
	A	B	C	D
1	IATA	ICAO	Name
2	AAA	NTGA	French Polynesia
3	ABE	KABE	Allentown PA
4	MEX	MMMX	Mexico City
5	ORD	KORD	Chicago IL
6
Sheet1

Sheet2(destnation)

Book3
	A	B	C	D
1	IATA	ICAO	Name
2	ABE	KABE	Allentown
3	ORD	KORD	Chicago
4	MEX	MMMX	Mexico
5	AAA	NTGA	French
6
Sheet2

A2, copied down:

=INDEX(Sheet1!$A$2:$A$5,MATCH(B2,Sheet1!$B$2:$B$5,0))

 

[Marc Enzi]

Brilliant

THankyou,

I sincerely appreciate it.