poadrim From Norway, joined Oct 2008, 174 posts, RR: 0 Posted (1 year 10 months 1 week 1 day 8 hours ago) and read 1890 times:
I have made my own "flight log" that I have been a passenger on. But I have a problem I can't get around.
I have 2 column that are named "ICAO" and "Airline", when I enter (let's say)
SAS in the "ICAO" column I want "Scandinavian Airlines" to appear in the "Airline" column.
Now, this is all good and well, but as I fly more then just SAS I want to add NAX (Norwegian Air Shuttle). This is here I get the problem.
=IF(E5:E500=SAS;”Scandinavian Airlines System”;"NOT BOOKED")
This works well, but I can't add another value for excel to look for.
=IF(E5:E500=SAS;”Scandinavian Airlines System”;IF(E5:E500=NAX;"Norwegian Air Shuttle";"NOT BOOKED"))
and get: #NAME?.
Good judgment comes from experience. Good experience comes from someone else's bad judgment.
planejamie From United Kingdom, joined Sep 2011, 576 posts, RR: 1
Reply 4, posted (1 year 10 months 4 days 12 hours ago) and read 1643 times:
Start by making a list of ICAO codes matched with airlines. Ideally have the ICAO codes in alphabetical order and put these in a little table type thing somewhere out of the way on the same sheet. You can hide the cells afterwards I believe. This will be used by the VLOOKUP function as a reference for basically, what to lookup when it is given the ICAO code.
If you press the little "fx" formuale/function symbol in Excel 2010 for the cell you put the full airline name into each time you have a new flight, a "Insert Function" popup will appear. Type "lookup" into the search box, hit "Go" and select "VLOOKUP" from the list. Click "OK"
Lookup Value - The cell you enter the ICAO code into each time (click the little button with a cell and red arrow on to select it)
Table Array - Again, click the little button on the right of the input box, choose the top left most cell of your ICAO/Airlines reference area and drag so it covers to the bottom right of it
Col_Index_Num will be the column in this reference area where the values are held. This is not the cell reference or column but a numerical value. So for this I would enter "2"
Ignore Range Lookup, hit "OK". Now you will have "N/A" appear in the cell, type the correct ICAO code into your ICAO cell and it should input the full name of the airline in the cell you put the VLOOKUP function into