Getting the City Name with account numbers

[Firasath]

Hi,

I have this scenario. I have the Account Numbers and I would like to find out the City the particular number is belongs to. Below I have mentioned the first characters of the numbers, which would be compulsorily there in each account, and their corresponding City abbreviations. I have tried it with OR and IF functions but it is not working in all cases since the compulsory first numbers length is in between 2 to 4. I tried with vlookup also, but it is also not giving desired results. Please note that the first compulsory number length could be in the range of 2 to 4 and the number could be similar like it could be 440 or 4414. Kindly suggest.

<table border="0" cellpadding="0" cellspacing="0" width="128"><colgroup><col style="width:48pt" width="64"> <col style="width:48pt" width="64"> </colgroup><tbody><tr style="height:15.0pt" height="20"> <td class="xl65" style="height:15.0pt;width:48pt" height="20" width="64">Code</td> <td style="width:48pt" width="64">City</td> </tr> <tr style="height:15.0pt" height="20"> <td class="xl64" style="height:15.0pt;width:48pt" height="20" width="64">02</td> <td>AUH</td> </tr> <tr style="height:15.0pt" height="20"> <td class="xl64" style="height:15.0pt;border-top:none; width:48pt" height="20" width="64">03</td> <td>AAN</td> </tr> <tr style="height:15.0pt" height="20"> <td class="xl64" style="height:15.0pt;border-top:none; width:48pt" height="20" width="64">04</td> <td>DXB</td> </tr> <tr style="height:15.0pt" height="20"> <td class="xl64" style="height:15.0pt;border-top:none; width:48pt" height="20" width="64">06</td> <td>WC</td> </tr> <tr style="height:15.0pt" height="20"> <td class="xl64" style="height:15.0pt;border-top:none; width:48pt" height="20" width="64">07</td> <td>RAK</td> </tr> <tr style="height:15.0pt" height="20"> <td class="xl64" style="height:15.0pt;border-top:none; width:48pt" height="20" width="64">09</td> <td>EC</td> </tr> <tr style="height:15.0pt" height="20"> <td class="xl64" style="height:15.0pt;border-top:none; width:48pt" height="20" width="64">12</td> <td>AUH</td> </tr> <tr style="height:15.0pt" height="20"> <td class="xl64" style="height:15.0pt;border-top:none; width:48pt" height="20" width="64">13</td> <td>AAN</td> </tr> <tr style="height:15.0pt" height="20"> <td class="xl64" style="height:15.0pt;border-top:none; width:48pt" height="20" width="64">14</td> <td>DXB</td> </tr> <tr style="height:15.0pt" height="20"> <td class="xl64" style="height:15.0pt;border-top:none; width:48pt" height="20" width="64">16</td> <td>WC</td> </tr> <tr style="height:15.0pt" height="20"> <td class="xl64" style="height:15.0pt;border-top:none; width:48pt" height="20" width="64">312</td> <td>AUH</td> </tr> <tr style="height:15.0pt" height="20"> <td class="xl64" style="height:15.0pt;border-top:none; width:48pt" height="20" width="64">314</td> <td>DXB</td> </tr> <tr style="height:15.0pt" height="20"> <td class="xl64" style="height:15.0pt;border-top:none; width:48pt" height="20" width="64">316</td> <td>WC</td> </tr> <tr style="height:15.0pt" height="20"> <td class="xl64" style="height:15.0pt;border-top:none; width:48pt" height="20" width="64">317</td> <td>RAK</td> </tr> <tr style="height:15.0pt" height="20"> <td class="xl64" style="height:15.0pt;border-top:none; width:48pt" height="20" width="64">319</td> <td>EC</td> </tr> <tr style="height:15.0pt" height="20"> <td class="xl64" style="height:15.0pt;border-top:none; width:48pt" height="20" width="64">42</td> <td>AUH</td> </tr> <tr style="height:15.0pt" height="20"> <td class="xl67" style="height:15.0pt;border-top:none" height="20">4213</td> <td>AUH</td> </tr> <tr style="height:15.0pt" height="20"> <td class="xl67" style="height:15.0pt;border-top:none" height="20">4214</td> <td>AUH</td> </tr> <tr style="height:15.0pt" height="20"> <td class="xl64" style="height:15.0pt;border-top:none; width:48pt" height="20" width="64">43</td> <td>AAN</td> </tr> <tr style="height:15.0pt" height="20"> <td class="xl67" style="height:15.0pt;border-top:none" height="20">4313</td> <td>AAN</td> </tr> <tr style="height:15.0pt" height="20"> <td class="xl67" style="height:15.0pt;border-top:none" height="20">4314</td> <td>AAN</td> </tr> <tr style="height:15.0pt" height="20"> <td class="xl64" style="height:15.0pt;border-top:none; width:48pt" height="20" width="64">44</td> <td>DXB</td> </tr> <tr style="height:15.0pt" height="20"> <td class="xl67" style="height:15.0pt;border-top:none" height="20">4413</td> <td>DXB</td> </tr> <tr style="height:15.0pt" height="20"> <td class="xl67" style="height:15.0pt;border-top:none" height="20">4414</td> <td>DXB</td> </tr> <tr style="height:15.0pt" height="20"> <td class="xl64" style="height:15.0pt;border-top:none; width:48pt" height="20" width="64">46</td> <td>WC</td> </tr> <tr style="height:15.0pt" height="20"> <td class="xl67" style="height:15.0pt;border-top:none" height="20">4613</td> <td>WC</td> </tr> <tr style="height:15.0pt" height="20"> <td class="xl67" style="height:15.0pt;border-top:none" height="20">4614</td> <td>WC</td> </tr> <tr style="height:15.0pt" height="20"> <td class="xl64" style="height:15.0pt;border-top:none; width:48pt" height="20" width="64">47</td> <td>RAK</td> </tr> <tr style="height:15.0pt" height="20"> <td class="xl67" style="height:15.0pt;border-top:none" height="20">4713</td> <td>RAK</td> </tr> <tr style="height:15.0pt" height="20"> <td class="xl67" style="height:15.0pt;border-top:none" height="20">4714</td> <td>RAK</td> </tr> <tr style="height:15.0pt" height="20"> <td class="xl64" style="height:15.0pt;border-top:none; width:48pt" height="20" width="64">49</td> <td>EC</td> </tr> <tr style="height:15.0pt" height="20"> <td class="xl67" style="height:15.0pt;border-top:none" height="20">4913</td> <td>EC</td> </tr> <tr style="height:15.0pt" height="20"> <td class="xl67" style="height:15.0pt;border-top:none" height="20">4914</td> <td>EC</td> </tr> <tr style="height:15.0pt" height="20"> <td class="xl64" style="height:15.0pt;border-top:none; width:48pt" height="20" width="64">52</td> <td>AUH</td> </tr> <tr style="height:15.0pt" height="20"> <td class="xl64" style="height:15.0pt;border-top:none; width:48pt" height="20" width="64">53</td> <td>AAN</td> </tr> <tr style="height:15.0pt" height="20"> <td class="xl64" style="height:15.0pt;border-top:none; width:48pt" height="20" width="64">54</td> <td>DXB</td> </tr> <tr style="height:15.0pt" height="20"> <td class="xl64" style="height:15.0pt;border-top:none; width:48pt" height="20" width="64">56</td> <td>WC</td> </tr> <tr style="height:15.0pt" height="20"> <td class="xl64" style="height:15.0pt;border-top:none; width:48pt" height="20" width="64">57</td> <td>RAK</td> </tr> <tr style="height:15.0pt" height="20"> <td class="xl64" style="height:15.0pt;border-top:none; width:48pt" height="20" width="64">59</td> <td>EC</td> </tr> <tr style="height:15.0pt" height="20"> <td class="xl64" style="height:15.0pt;border-top:none; width:48pt" height="20" width="64">62</td> <td>AUH</td> </tr> <tr style="height:15.0pt" height="20"> <td class="xl64" style="height:15.0pt;border-top:none; width:48pt" height="20" width="64">63</td> <td>AAN</td> </tr> <tr style="height:15.0pt" height="20"> <td class="xl64" style="height:15.0pt;border-top:none; width:48pt" height="20" width="64">64</td> <td>DXB</td> </tr> <tr style="height:15.0pt" height="20"> <td class="xl64" style="height:15.0pt;border-top:none; width:48pt" height="20" width="64">66</td> <td>WC</td> </tr> <tr style="height:15.0pt" height="20"> <td class="xl64" style="height:15.0pt;border-top:none; width:48pt" height="20" width="64">67</td> <td>RAK</td> </tr> <tr style="height:15.0pt" height="20"> <td class="xl64" style="height:15.0pt;border-top:none; width:48pt" height="20" width="64">69</td> <td>AAN</td> </tr> <tr style="height:15.0pt" height="20"> <td class="xl64" style="height:15.0pt;border-top:none; width:48pt" height="20" width="64">712</td> <td>AUH</td> </tr> <tr style="height:15.0pt" height="20"> <td class="xl64" style="height:15.0pt;border-top:none; width:48pt" height="20" width="64">713</td> <td>AAN</td> </tr> <tr style="height:15.0pt" height="20"> <td class="xl64" style="height:15.0pt;border-top:none; width:48pt" height="20" width="64">714</td> <td>DXB</td> </tr> <tr style="height:15.0pt" height="20"> <td class="xl64" style="height:15.0pt;border-top:none; width:48pt" height="20" width="64">716</td> <td>WC</td> </tr> <tr style="height:15.0pt" height="20"> <td class="xl64" style="height:15.0pt;border-top:none; width:48pt" height="20" width="64">717</td> <td>RAK</td> </tr> <tr style="height:15.0pt" height="20"> <td class="xl64" style="height:15.0pt;border-top:none; width:48pt" height="20" width="64">719</td> <td>EC</td> </tr> <tr style="height:15.0pt" height="20"> <td class="xl66" style="height:15.0pt;width:48pt" height="20" width="64">72</td> <td>AUH</td> </tr> <tr style="height:15.0pt" height="20"> <td class="xl66" style="height:15.0pt;width:48pt" height="20" width="64">73</td> <td>AAN</td> </tr> <tr style="height:15.0pt" height="20"> <td class="xl66" style="height:15.0pt;width:48pt" height="20" width="64">74</td> <td>DXB</td> </tr> <tr style="height:15.0pt" height="20"> <td class="xl66" style="height:15.0pt;width:48pt" height="20" width="64">76</td> <td>WC</td> </tr> <tr style="height:15.0pt" height="20"> <td class="xl66" style="height:15.0pt;width:48pt" height="20" width="64">77</td> <td>RAK</td> </tr> <tr style="height:15.0pt" height="20"> <td class="xl66" style="height:15.0pt;width:48pt" height="20" width="64">79</td> <td>EC</td> </tr> <tr style="height:15.0pt" height="20"> <td class="xl66" style="height:15.0pt;width:48pt" height="20" width="64">82</td> <td>AUH</td> </tr> <tr style="height:15.0pt" height="20"> <td class="xl66" style="height:15.0pt;width:48pt" height="20" width="64">83</td> <td>AAN</td> </tr> <tr style="height:15.0pt" height="20"> <td class="xl66" style="height:15.0pt;width:48pt" height="20" width="64">84</td> <td>DXB</td> </tr> <tr style="height:15.0pt" height="20"> <td class="xl66" style="height:15.0pt;width:48pt" height="20" width="64">86</td> <td>WC</td> </tr> <tr style="height:15.0pt" height="20"> <td class="xl66" style="height:15.0pt;width:48pt" height="20" width="64">87</td> <td>RAK</td> </tr> <tr style="height:15.0pt" height="20"> <td class="xl66" style="height:15.0pt;width:48pt" height="20" width="64">89</td> <td>EC</td> </tr> </tbody></table>

 

[jim may]

Are you code numbers (Column A) "Text" or true numbers?

 

[Firasath]

Its in the General format.

 

[jim may]

Let's start here. What needs to be done to this to get what you want?

Excel Workbook
	A	B	C	D	E
1	Code	City		Lookup?	City ='s
2	2	AUH		4213	AUH
3	3	AAN
4	312	AUH
5	314	DXB
6	4213	AUH
7	4214	AUH
Sheet1

Excel 2007

Cell Formulas
Range	Formula
E2	=VLOOKUP(D2,$A$2:$B$7,2,FALSE)

 

[Firasath]

Sorry, if I could not explain it before properly. The actual account numbers length is more then the once I have specified. It could be 9 characters or it could be 7 characters. Like 421354054 is one of the number and similarly the other number could be 045256322. In above first 4 digits i.e 4213 would remain constant for AUH City and in the second example 04 will remain constant since those are the area codes. I know I need to use the LEFT function, but I am not able to put it properly along with vlookup or if commands. Please advise.

 

[T. Valko]

Sorry, if I could not explain it before properly. The actual account numbers length is more then the once I have specified. It could be 9 characters or it could be 7 characters. Like 421354054 is one of the number and similarly the other number could be 045256322. In above first 4 digits i.e 4213 would remain constant for AUH City and in the second example 04 will remain constant since those are the area codes. I know I need to use the LEFT function, but I am not able to put it properly along with vlookup or if commands. Please advise.

See if this helps:

http://www.mrexcel.com/forum/showthread.php?t=556887&highlight=44121276

 

[mikerickson]

If the full (7-9 character) account number is in A1 would
=LEFT(A1, MAX(0, LEN(A1)-5)) extract the prefix that you want to lookup?