Vlookup from First Three Characters of Codes

[prkhan56]

I have posted this on Excel WorksheetFunctions.
No help yet.. therefore posting it here.. Can somebody help me
Thanks


Hello All,


I am using Windows XP/Office 2003 and have the following problem:


I have a Sheet Name 'Customers' with a Dynamic Range defined as
'Codes' (Column A and Column B) as follows:


A B
APL Astro Plastics Limited
AFC Allan Feed Company
....
....
....
etc etc


In another Sheet I use Vlookup to extract values using the following
formula:


=IF(ISNA(VLOOKUP(A2,Codes,2,FA­LSE)),"",(VLOOKUP(A2,Codes,2,F­ALSE)))



The above formula works fine. Now I wish to modify the above to look
for the first three digits of an ID entered as eg APL176SCTP (APL in
this case) and give me the name of the Company as Astro Plastics
Limited..


I wish to extract the Name from Column B in Customers Sheet depending
on the first three digits of the ID entered. I hope I am clear


Can any body give me a clue or suggestions please.

 

[JulieD]

Hi

just for future reference - as far as i know
microsoft.public.worksheetfunctions is not an active group -
microsoft.public.worksheet.functions however is

but to answer your question
try
=IF(ISNA(VLOOKUP(LEFT(A2,3),Codes,2,FA­LSE)),"",(VLOOKUP(LEFT(A2,3),Codes,2,F­ALSE)))

Cheers
JulieD

I have posted this on Excel WorksheetFunctions.
No help yet.. therefore posting it here.. Can somebody help me
Thanks


Hello All,


I am using Windows XP/Office 2003 and have the following problem:


I have a Sheet Name 'Customers' with a Dynamic Range defined as
'Codes' (Column A and Column B) as follows:


A B
APL Astro Plastics Limited
AFC Allan Feed Company
....
....
....
etc etc


In another Sheet I use Vlookup to extract values using the following
formula:


=IF(ISNA(VLOOKUP(A2,Codes,2,FA­LSE)),"",(VLOOKUP(A2,Codes,2,F­ALSE)))



The above formula works fine. Now I wish to modify the above to look
for the first three digits of an ID entered as eg APL176SCTP (APL in
this case) and give me the name of the Company as Astro Plastics
Limited..


I wish to extract the Name from Column B in Customers Sheet depending
on the first three digits of the ID entered. I hope I am clear


Can any body give me a clue or suggestions please.

 

[prkhan56]

Thnaks Julie, it works fine

 

[JulieD]

you're welcome and thanks for the feedback

Thnaks Julie, it works fine

 

[Wild Jim]

You need to use LEFT in your formula:


=IF(ISNA(VLOOKUP(LEFT(A4,3),codes,2,FALSE)),"",(VLOOKUP(LEFT(A4,3),codes,2,F
ALSE)))


I have posted this on Excel WorksheetFunctions.
No help yet.. therefore posting it here.. Can somebody help me
Thanks


Hello All,


I am using Windows XP/Office 2003 and have the following problem:


I have a Sheet Name 'Customers' with a Dynamic Range defined as
'Codes' (Column A and Column B) as follows:


A B
APL Astro Plastics Limited
AFC Allan Feed Company
....
....
....
etc etc


In another Sheet I use Vlookup to extract values using the following
formula:


=IF(ISNA(VLOOKUP(A2,Codes,2,FA­LSE)),"",(VLOOKUP(A2,Codes,2,F­ALSE)))



The above formula works fine. Now I wish to modify the above to look
for the first three digits of an ID entered as eg APL176SCTP (APL in
this case) and give me the name of the Company as Astro Plastics
Limited..


I wish to extract the Name from Column B in Customers Sheet depending
on the first three digits of the ID entered. I hope I am clear


Can any body give me a clue or suggestions please.

 

[Harlan Grove]

JulieD wrote...

just for future reference - as far as i know
microsoft.public.worksheetfunctions is not an active group -
microsoft.public.worksheet.functions however is

....

Actually neither of these is a newsgroup. The newsgroups are

microsoft.public.excel.worksheetfunctions
microsoft.public.excel.worksheet.functions

Microsoft may have discontinued the former on the msnews.microsoft.com
NNTP servers, but it continues to exist in USENET. Maybe not as active
as this ng, but not inactive.

 

[JulieD]

Hi Harlan

thanks for the correction ...

Cheers
JulieD