ExcelBanter - Vlookup combined with LEFT function

ExcelBanter (https://www.excelbanter.com/)

- Excel Worksheet Functions (https://www.excelbanter.com/excel-worksheet-functions/)

- - Vlookup combined with LEFT function (https://www.excelbanter.com/excel-worksheet-functions/222909-vlookup-combined-left-function.html)

Vlookup combined with LEFT function

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.

Vlookup combined with LEFT function

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

"Shams" wrote:

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.