Vlookup combined with LEFT function

  • Thread starter Thread starter Shams
  • Start date Start date
S

Shams

I have a set of numbers like the following:
Column A
5848606
9659285
8100949
4846771
7201303

Now I have another set of data in the following format:
Column B Column C
# PROVINCE
0 NF
1 PE
2 NS
3 NB
4 QC
5 ON
6 MB


My challenge is to be able to match the first character in the initial set
of data with the data in the array below and then if it finds it, it should
bring back the Province information. For example, 5848606 should return ON.

Now, I thought that I could do the following:
=vlookup(left(A1,1),$B$1:$C$8,2,FALSE)

This formula seems pretty intuitive but I am getting a #N/A. I am not sure
why this is happening. Hoping to get some tips. Thanks.

Shams.
 
hi,

It's not working because your comparing a string with a number, try

=VLOOKUP(LEFT(A1,1)*1,$B$1:$C$8,2,FALSE)

Mike
 
Back
Top