Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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. |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
If Function combined with OR and LEFT | Excel Worksheet Functions | |||
VLOOKUP Function and data combined from 2 cells | Excel Worksheet Functions | |||
vlookup combined with AND-function | Excel Worksheet Functions | |||
Combined VLOOKUP AND BETWEEN FUNCTION | Excel Worksheet Functions | |||
vlookup / len function combined | Excel Discussion (Misc queries) |