Excel cell value

[jminn0311]

How can you make a cell's value based on another cell's value. For Example:

If cell B1 is "Miami" I want cell A1 to be "MIA". I know how to do this with the IF formula but that only works for 1 condition. The problem is cell B1 might be "Dallas" and then I would want A1 to be "DAL"

Thank you for your help

Jeff

 

[AnotherHiggins]

Do you always want the first three letters of B1?

If so, type the following into cell A1:
=left(B1,3)

[tt]-John[/tt]
________________________
To get the best answers fast, please read faq181-2886

 

[bluedragon2]

And, if you want them upper case, =UPPER(LEFT(B1,3))



[Blue]Blue[/Blue] [Dragon]

If I wasn't Blue, I would just be a Dragon...

 

[AnotherHiggins]

Good eye, blue. I missed that in the post.

[tt]-John[/tt]
________________________
To get the best answers fast, please read faq181-2886

 

[jminn0311]

Unfortunately it is not that easy. I just used the city names as an example. The actual cell values will be linked to coded numeric values. The formula in A1 will read something to the effect of

If(b1="Dog",1245)OR IF(b1="cat",1247)OR etc....

Does anyone know how to make something like this work?

Thank you for all the reply's.

Jeff

 

[KenWright]

You need VLOOKUP. Take a look at my FAQ on this function:-

faq68-4743

Regards
Ken............

----------------------------------------------------------------------------
It's easier to beg forgiveness than ask permission

----------------------------------------------------------------------------

 

[AnotherHiggins]

Two options: if you only have a few things to look for, then the syntax for nested IFs is like this:
=if(b1="dog",1245,if(b1="cat",1247,""))

If, on the other hand, you have many values to look for, have a look at VLookup.

On a second sheet (hidden, if you would like), you can create a list with Dog & Cat in column A; and 1245 & 1247 in column B. Highlight the columns and name the range something - I'll say "RngLookup" for this example.

On the sheet where values will be inputted, use this formula in cell A1:
=vlookup(B1,RngLookup,2,0)

If you want to have the formula in rows where column B is empty without seeing the #N/A error, then you could use this:
=if(iserror(vlookup(b1,RngLookup,2,0)),"",vlookup(b1,RngLookup,2,0))

[tt]-John[/tt]
________________________
To get the best answers fast, please read faq181-2886