Vlookup from First Three Characters of Codes

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
..=2E.
..=2E.
..=2E.
etc etc


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


=3DIF(ISNA(VLOOKUP(A2,Codes,2,FA=ADLSE)),"",(VLOOKUP(A2,Codes,2,F=ADALSE)))



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.

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

"prkhan56" <[email protected]> wrote in message
news:[email protected]...
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.

Thnaks Julie, it works fine
Rashid
JulieD wrote:
> 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
>
=3DIF(ISNA(VLOOKUP(LEFT(A2,3),Codes,2,FA=ADLSE)),"",(VLOOKUP(LEFT(A2,3),Cod=
es,2,F=ADALSE)))
>
> Cheers
> JulieD
>
> "prkhan56" <[email protected]> wrote in message
> news:[email protected]...
> 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:
>
>
>
=3DIF(ISNA(VLOOKUP(A2,Codes,2,FA=ADLSE)),"",(VLOOKUP(A2,Codes,2,F=ADALSE)))
>
>
>
> 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
>=20
>=20
> Can any body give me a clue or suggestions please.

you're welcome and thanks for the feedback

"prkhan56" <[email protected]> wrote in message
news:[email protected]...
Thnaks Julie, it works fine
Rashid
JulieD wrote:
> 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
>
> "prkhan56" <[email protected]> wrote in message
> news:[email protected]...
> 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.

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)))


"prkhan56" <[email protected]> wrote in message
news:[email protected]...
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 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.

Hi Harlan

thanks for the correction ...

Cheers
JulieD

"Harlan Grove" <[email protected]> wrote in message
news:[email protected]...
> 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.
>