Using VLookup with Left function

I receive workbooks containing invoice information. One of the many taskes is to allocate costs to company and cost codes.

I have attached a very simple example of what I am trying to do.

I've created a two column range and called it Identifier. The first column is the number I look up and the second column is the number I want to return.

The formulae I am using in B2 is:

=VLOOKUP(LEFT(A2,1),Identifier,2,0)

This should find the first character of the string in A2 look in column 1 of my table "Identifier" and return column 2.

If the first character is an alpha character the formulae works, if it is a numeric digit it returns #N/A, so the problem is obviously with text versus number.

Can anyone help with a solution?

You are looking up and using a mix of numbers and text/number strings...

You have to convert all to text via Data|Text to columns, skipping to 3rd window and select Text, then Finish

or change formula to:

=VLOOKUP(IF(ISNUMBER(LEFT(A2,1)+0),LEFT(A2,1)+0,LEFT(A2,1)),Identifier,2,0)