Vlookup on first two characters

[steve in los gatos]

I would like to do a vlookup that looks only at the first
two charaters (numerals), Comparing that value to a list
to catergorize the account!

Accounts Lookup table
66411 66 Software Sales
66700 68 Hardware Sales
68302 69 Other Revenues
69300

So in this example, I need the lookup to categorize
accounts 66411 and 66700 as "Software Sales". The first
two digits being the lookup value. Any help here greatly
appreciated!
Steve
In beautiful downtown Los Gatos CA

 

[Soo Cheon Jheong]

Hi,

Assuming that your lookup table is contained in D2:E4.


=VLOOKUP(VALUE(LEFT(A2,2)),$D$2:$E$4,2,FALSE)


--
Regards,
Soo Cheon Jheong
_ _
^¢¯^
--

 

[Frank Kabel]

Hi
try the array formula (entered with CTRL+SHIFT+ENTER):
=INDEX(C1:C100,MATCH(LEFT(lookup_value,2),LEFT(B1:B100),0))

 

[Domenic]

Assuming that your lookup table is contained in D2:E4...

=VLOOKUP(--LEFT(A2,2),$D$2:$E$4,2,0)

Hope this helps!

 

[Ken Wright]

Frank's given you one method, another is to use VLOOKUP as so

=VLOOKUP(--LEFT(A3,2),$C$3:$D$5,2,0)

where you effectively take the first two characters and then convert them to
numeric for purposes of the lookup.

Another option is to put in another column, copy in the Accounts data and then
just do a text to columns on it / fixed width picking up just the first two
numbers. Having the extra field can sometimes pay dividends depending on what
you are doing.

 

[Rob]

Frank,

I tried your suggestion for something I am doing but couldn't figure out
which elements of the formula referred to what. Could you please elaborate.

Thanks, Rob

 

[Frank Kabel]

Hi
- column C the column to return the value from
- column B the column to search for 'lookuop_value'

 

[Rob]

Frank,

Still having problems with array formula. Can you please help. Using the
example originally posted by Steve, Accounts are in cells B10:B13 and the
lookup table is D5:E7. I assume lookup table is in two columns, but if not
would I combine the number and text into say column D.

Would you be kind enough to post what the array formula would be for the
above.

Many thanks, Rob