Vlookup combined with LEFT function

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.
 
M

Mike H

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
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top