Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 47
Default 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   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 11,501
Default 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
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
If Function combined with OR and LEFT JRJ Excel Worksheet Functions 4 October 6th 08 09:38 PM
VLOOKUP Function and data combined from 2 cells MsBeverlee Excel Worksheet Functions 2 March 11th 07 11:06 PM
vlookup combined with AND-function martho Excel Worksheet Functions 1 January 20th 06 01:49 PM
Combined VLOOKUP AND BETWEEN FUNCTION Louis Markowski Excel Worksheet Functions 3 October 13th 05 03:54 PM
vlookup / len function combined jamesg-fid Excel Discussion (Misc queries) 3 March 10th 05 04:27 PM


All times are GMT +1. The time now is 05:25 PM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"