YTL Excel #113: VLOOKUP RIGHT 5 characters only

I am trying to do a Vlookup with a string that has the | (pipe) and ~ (tilde) characters in it.

Vlookup doesn't appear to recognize or be able to do a match on these character.

My string looks something like this - 112|a3457|lmpop|~|~

And yes, I do need all these elements.

I've formatted both the target and source to be consistent.

I've copied a cell from the target and put it in the source and the Vlookup didn't recognize it.

I've tried a find and replace on these characters and Find didn't find them.

I've an ugly feeling that I'm doomed.

Any ideas, anyone?

I am trying to use this formula to do a vlookup using only the first 4 digits of the cell. However it is coming up as N/A. Anyone have any ideas of how to do this?

Code:

=VLOOKUP(LEFT(A5,4),Customers,3,FALSE)

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.

I have a list of text strings in Col A and Col B. I am using =Vlookup(A1,B$1:B$2000,1,False) to lookup Col A value in Col B array. For values in Col A that are greater than 255 characters in length, I get a #VALUE error. How can I get around by using another function or modifying the vlookup in some way.

Greetings,

I'm attempting to use Vlookup to find order numbers and the orders date of completion. However the reports that I am using are formatted differently so the order numbers are not exact. One spreadsheet lists the internal order number and the other lists the external order number. IE 123456
8119(123456001) Is it possible to specify a string of numbers within a cell using vlookup? Or should I look at another function?

I've attached an example. Thanks in advance for your help!

Hi

Any help with the following would be greatly appreciated -
I'm trying to do a vlookup whereby it only searches/has to match the first 4
characters in the utmost left column of the table and return the associated
cell. My non working formula is:

=VLOOKUP(A:A(left4),EmailList!A:C,3,FALSE)
whereby the A:A(left4) is meant to be the first 4 characters from the left
of the column.

Thks
Gus

Ok, I'm back for round two. I thought I had this issue resolved (thanks to sglife and modytrane), but now another variable has crept into the picture. Mind you, I'm no Excel wiz and don't create formulas on a daily basis, (otherwise this stuff would be retained much longer), but I have created some rather complex formulas in the past (don't ask me how, just pure luck I guess). I believe the issue I'm having right now is super basic, but I just can't seem to figure it out.

Here's my situation: I have two worksheets, one on the server and one on my hard drive (let's call them #1 and #2, respectively) I'm trying to reference a serial number on #1 (the serial number on #2 may be B1234W1R2C3H456, while the number in #1 may be B1234W1R2C3H456N1) Not all of the serial numbers on #1 have a 2-digit suffix Not all of the serial numbers on #2 exist on #1
Questions: How can I tell Excel to look for a serial number in #1 but ignore the 2-digit suffix, if one exists? How can I tell Excel to leave the cell in #2 blank if it is unable to find the referenced serial number in #1?
This is the formula I'm using:
Code:

=VLOOKUP(B72,'\\xx.x.x.xx\Production\[_Results.xlsx]Sheet1'!$A$1:$P$500,4,FALSE)

Thanks for any help you are able to provide!

Chris

I am trying to get a vlookup where I need to match column c from sheet 1 to column a from sheet 2 to return column c from sheet 2 but the problem is that column c on sheet 1 has 4 characters more then column a on sheet 2 is there any way to get vlookup to start the lookup from column a after the first 4 characters ?

hi,

I'm currently having trouble with a formula for using the last 7 numbers in a cell to lookup this string of numbers in another workbook.

For example in workbook1 the user enters an order number: 1236541
I have another cell which takes this number and looks into our orders schedule workbook and then populates a number of other cells with certain job information.

But ever since our scheduling department started to add letters and numbers to the beginning of these order numbers, eg: PLO/1236541
the cells no longer get populated because its only looking for the exact number.

i currently have this as my formula: =VLOOKUP(VALUE(RIGHT(C12,7)),'[MOULDINGS_Current_month_schedule.xls]410'!$E$8:$H$100,2,FALSE)

Any help would be much appreciated, Thanks.

Just as subject says. I'm trying to use VLOOKUP to only reference the first 8 characters in reference field.

Here is my original formula: =VLOOKUP(A:A,'108 cross ref'!A:B,2,FALSE)

My array looks like this:

Column A / Column B

905-3407-045 / 605-3456


I only want to reference 905-3407......not the rest, or -045.


Thanks, Michael