poadrim From Norway, joined Oct 2008, 173 posts, RR: 0 Posted (4 months 7 hours ago) and read 853 times:
Hi guys,
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.
I tried:
=IF(E5:E500=SAS;”Scandinavian Airlines System”;IF(E5:E500=NAX;"Norwegian Air Shuttle";"NOT BOOKED"))
and get: #NAME?.
Any ideas?
Thanks guys!!
Good judgment comes from experience. Good experience comes from someone else's bad judgment.
TLG From United States of America, joined Jul 2005, 324 posts, RR: 0 Reply 2, posted (4 months 4 hours ago) and read 802 times:
Your formulae aren't Excel; are you sure you're not using a different spreadsheet? I'm familiar with Excel only, so I can't identify what it is exactly.
I edited the nested formula a bit to fit the Excel syntax, and it works:
=IF(E5="SAS","Scandinavian Airlines System",IF(E5="NAX","Norwegian Air Shuttle","Not Booked"))
The range (E5:500) isn't necessary; Row 5 should be E5, Row 6 should be E6, and so on.
Maybe I'm totally missing what you're trying to do here. If that's the case, then my response will be invalid.
poadrim From Norway, joined Oct 2008, 173 posts, RR: 0 Reply 3, posted (3 months 4 weeks 10 hours ago) and read 686 times:
Hi, and thanks so far guys Appreciate it!
Well TLG, I am using MS Excel 2010. So I don't know why my formulas come back like this but your formula works, so thanks!
Quoting TLG (Reply 2): Your formulae aren't Excel; are you sure you're not using a different spreadsheet? I'm familiar with Excel only, so I can't identify what it is exactly.
I edited the nested formula a bit to fit the Excel syntax, and it works:
=IF(E5="SAS","Scandinavian Airlines System",IF(E5="NAX","Norwegian Air Shuttle","Not Booked"))
The range (E5:500) isn't necessary; Row 5 should be E5, Row 6 should be E6, and so on.
Maybe I'm totally missing what you're trying to do here. If that's the case, then my response will be invalid.
WOW Dreadnought! That was some formula you wrote for me. Thanks! Just a minor thing, doesn't work thou
As it seams you know this I'm just gonna PM you. And already now, thank you for your help!
planejamie From United Kingdom, joined Sep 2011, 572 posts, RR: 0 Reply 4, posted (3 months 3 weeks 5 days 10 hours ago) and read 606 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
Dreadnought From United States of America, joined Feb 2008, 7749 posts, RR: 22 Reply 5, posted (3 months 3 weeks 5 days 10 hours ago) and read 591 times:
planejamie From United Kingdom, joined Sep 2011, 572 posts, RR: 0 Reply 6, posted (3 months 3 weeks 4 days 19 hours ago) and read 558 times:
Quoting Dreadnought (Reply 5): Don't ignore it. If you do, it will default to "TRUE", and will give you approximate results. "FALSE" forces VLOOKUP to only return exact matches.
Ah right I didn't know that, this has just explained a lot! haha
Sponsor Message: 
From Norway, joined Oct 2008, 173 posts, 
From United States of America, joined Feb 2008, 7749 posts, 
Appreciate it! 
From United Kingdom, joined Sep 2011, 572 posts, 
Printer friendly format 
