Search for a partial number

  • Thread starter Thread starter das
  • Start date Start date
D

das

I have account numbers and the first three numbers determine the location of
the account. What I would like is to have a formula that reads the first
three numbers of the of all the account number and then assign the name of
the account in an adjacent cell.

9895254 would be a "Retail" account
8892532 would be an "Expense" account
etc...
 
Easiest way would be to set up a lookup table. Suppose you create a list of
the first 3 numbers and their corresponding descriptions in range D1:E5. It
might look like
D E
988 Retail
989 Expense
....

Then assume your full length account numbers are in A1:A1000, create the
following formula in B1 and copy it down as needed.

=VLOOKUP(LEFT(A1,3),$D$1:$E$5,2,FALSE)

This will work as is IF the account numbers are text entries, if they are
numbers then change the formula:

=VLOOKUP(VALUE(LEFT(A1,3)),$D$1:$E$5,2,FALSE)

or make the entries in the first column of the range D1:E5 text also. The
data types must match.
 
I have account numbers and the first three numbers determine the location of
the account. What I would like is to have a formula that reads the first
three numbers of the of all the account number and then assign the name of
the account in an adjacent cell.

9895254 would be a "Retail" account
8892532 would be an "Expense" account
etc...
Click to expand...

Use the VLOOKUP function, with lookup_value being either LEFT(account_number,3)
or, if the values in your lookup table are numbers, then
--LEFT(account_number,3)
--ron
 
Back
Top