How to do this IF?

bpflyr

Board Regular
Joined
Nov 7, 2005
Messages
116
I have a list of about 200 rows with different 3 letter codes. There are about 150 different codes. How can I get Excel to:

If B1=TEB then return "Teterboro, NJ" in cell D1.
If B1=LGA then return "New York, NY" in cell D1.

So forth and so on for all 150 different code posibilities.

I will be entering the data in column B, but need Excel to return the answer in D.

How do I or can I do this?? Thank you so much in advance.
 

Some videos you may like

Excel Facts

How can you turn a range sideways?
Copy the range. Select a blank cell. Right-click, Paste Special, then choose Transpose.

Fat Cat

Active Member
Joined
Nov 5, 2004
Messages
336
a quick way would be on a separate sheet to put the 3 letter codes in one column and the place names beside them in a second column

Then do a Lookup based on the value in B1 returning the answer to D1

eg;
=LOOKUP(B1, range of values to lookup , range of values to return )
 

iggydarsa

Well-known Member
Joined
Jun 28, 2005
Messages
1,647
I would go with Fat Cat's idea... it would be easier to modify as well
 

Aladin Akyurek

MrExcel MVP
Joined
Feb 14, 2002
Messages
85,201
Fat Cat said:
a quick way would be on a separate sheet to put the 3 letter codes in one column and the place names beside them in a second column

Then do a Lookup based on the value in B1 returning the answer to D1

eg;
=LOOKUP(B1, range of values to lookup , range of values to return )

And sort the 2-column table (say in F2:G100) on its first column if you want to invoke a LOOKUP formula. BTW, such a formula needs to be carefully crafted:

=LOOKUP(B1,$F$2:$F$10,$G$2:$G$100)

if B1 is selected from a dropdown list, constructed from the first column of the sorted table. Otherwise:

=IF(LOOKUP(B1,$F$2:$F$10)=B1,LOOKUP(B1,$F$2:$F$10,$G$2:$G$100),"Not Found")
 

Watch MrExcel Video

Forum statistics

Threads
1,118,284
Messages
5,571,307
Members
412,381
Latest member
RogerL
Top