How can I have excel bring back a specific value by only reading the first 8 digits of an account number.

[kbdavis11]

Here is what I would like to do:

Input an account number in A1

I would like B1 to have a formula to determine what type of account it is.

* * * *

I want to insert a 15-16 digit account number into A1. But only the first 8 digits of that account number is all I want to determine the type of account it is.

So, let's say account #s 11111111XXXXXXXX, 22222222XXXXXXXX, 33333333XXXXXXXX, etc are all ABC account, so I want B1 to display "ABC" if it meets the criteria (There are 7 different account numbers total that would fall under this criteria).

If the criteria is not met for the above 7 account numbers, then I would like Excel to automatically assume its XYZ account, and display "XYZ" in B1.

Remember, only the First Eight of the account number should be used into figuring out the account type, but I need all 15-16 digits of the account number to be in the A1 cell.

If a formula will not help, perhaps something in VBA?

Thanks so much in advance! I hope this thing I am attempting to do is possible!

 

[kbdavis11]

Oh btw, I am using Excel 2010 & Windows 7 if that makes a difference at all

 

[yesterdays]

I think we could do this by formula, please post sample of your criteria, a small table will help

 

[kbdavis11]

Thanks for the reply. Obviously I can't use real accounts numbers in my example; if I lose my job for that than I wouldn't need this spreadsheet lol. But here is an example of what I am looking for:

[FONT=courier new]Account # (A:A)    Branch (B:B)
----------------   --------------------------
[B]80150001[/B]65487016   ABC Bank
[B]80150002[/B]68768540   ABC Bank
[B]80150003[/B]56780654   ABC Bank
[B]80150004[/B]85876870   ABC Bank
[B]80013101[/B]50564065   ABC Bank
[B]80013102[/B]56456504   ABC Bank
[/FONT]

So, all account numbers with the prefixes:
80150001
80150002
80150003
80150004
80013101
80013102
will automatically show in the B column "ABC Bank"

If any account number starts with an 8-digit prefix that is not what is listed above will automatically show "XYZ Bank"

If you have a formula that will make this happen, I can take it from there. Thanks again for the reply! If you need anything else from me let me know.

 

[Blade Hunter]

Use a lookup table where you can maintain a list of account prefix's and the branch, this way you can add and remove from the list at will.

 

[yesterdays]

As Blade Hunter said, a list to look up will be better,
But here is a solution which hard code the criteria just in case :
B2:
=IF(PRODUCT(--ISERROR(FIND({80150001,80150002,80150003,80150004,80013101,80013102},G1)))=0,"ABC Bank","XYZ Bank")
Ctrl Shift Enter

 

[kbdavis11]

As Blade Hunter said, a list to look up will be better,
But here is a solution which hard code the criteria just in case :
B2:
=IF(PRODUCT(--ISERROR(FIND({80150001,80150002,80150003,80150004,80013101,80013102},G1)))=0,"ABC Bank","XYZ Bank")
Ctrl Shift Enter

That seems to be working. Thanks so much for the help!