# [SOLVED] Auto fill column (text) based on value in another column



## LG2 (Jan 25, 2010)

Excel 2010

I have added a (blank) column A to my sheet.

In column B, there is a list of alpha/numeric customer account numbers. Each office has an individual letter identifier, which is in the first position of the customer number (consistant). I need to enter the name of the office in column A, based on the customer number in column B.

Example:
Downtown (office name) Q456123 (customer number)
Downtown Q789456
Northside N123987
Northside N456321
Westside W654789
Westside W987123

IF column B starts with "Q", enter "Downtown" in column A
IF column B starts with "N", enter "Northside" in column A

The alpha identifier is not logical, for all of the offices, that is why I showed a non-logical example (downtown = Q). 

Is there a formula that will do this? I have tried many ways and cannot figure it out.

Thank you for your help!


----------



## AlbertMC2 (Jul 15, 2010)

*Re: Auto fill column (text) based on value in another column*

Create a table lets say in cells E1-F3 that relates your alpha identifier with the name of the office

```
Q	Downtown
N	Northside
W	Westside
```
then in the A column you would have 

```
=VLOOKUP(MID(B1,1,1),E$1:F$3,2,FALSE)
```
where 
*MID(B1,1,1)* gets the first character in your customer account number
*E$1:F$3* is the table that relates alpha codes to office names


----------



## LG2 (Jan 25, 2010)

*Re: Auto fill column (text) based on value in another column*

Beautiful! Albert, thank you very much, the VLOOKUP worked like a champ.

:beerchug:

I am not very good with the VLOOKUP formulas, any information I look at assumes the reader is already familiar. Is there a good reference that may be able to help me? For instance, the formula works, but I do not understand the "MID"; "1,1"; "2,FALSE". If I could understand this (and/or anything else) better, I may be able to put together my own formatting.

Thank you, again, I appreciate your time and assistance.

:thanx:


----------



## LG2 (Jan 25, 2010)

P.S.
For anyone who is looking for the same type of formatting, I did not create a table, I just entered the appropriate letter in a column with the corresponding name in the next column. In the formula, where E$1:F$3 is (in the example), I used the correct cell identifiers for the data I entered.

Another hint, I copied the formula down the column and, since I will be using this daily, I recorded a macro to insert another column (B), copied column A & pasted values only into column B then deleted column A and the columns where I had the original alpha and office name. Now, it is clean and I can copy the data over into my final report.


----------

