# Thread: VLookup With LEFT Function (2002 SP3)

1. ## VLookup With LEFT Function (2002 SP3)

Is it possible to do a Vlookup on a specific set of characters in a string? A sample string would be Project=605 (), Cost Center=61205, where I want to do the lookup on the project number - 605 in this case. The number will always be in the same position. I want to lookup the name of the project in a list. I think there is a way to do a LEFT function and ask for specific positions (9-11 in this case) but haven't been able to figure it out. I'm thinking it would be a Vlookup(Left(..... ??

TIA

2. ## Re: VLookup With LEFT Function (2002 SP3)

The MID() function is probably what you are looking for. It takes 2 arguments, the starting position and the length (number of characters). In your exampole MID("Project=605",9,3) would return 605. This value would be a string so you may need to either convert to a numeric withh the VALUE() function, or ensure the lookup numbers are stored as text. To handle "Cost Center=61205", you would need to pass the MID function different arguments, 13 and 5.

Andrew C

3. ## Re: VLookup With LEFT Function (2002 SP3)

The LEFT(), MID(), and RIGHT() functions can not work with arrays, therefore, if your table has that string in it you will need to add a couple of columns to your table. I have attached a workbook that shows how to do it. Note that columns B:C and F:G are formatted as text. The VLOOKUP formula are in H1:H2.

4. ## Re: VLookup With LEFT Function (2002 SP3)

I have modified your example to use array formulas with MID() and RIGHT(). It uses the INDEX / MATCH combination instead of VLOOKUP in the array formula to achieve the same result.

5. ## Re: VLookup With LEFT Function (2002 SP3)

To make this formula more dynamic, in case the length of the lookup project number is not consistent, you can substitute Len(F1) for the '3' in the first formula, or Len(G1) for the '5' in the second formula - still entered as arrays, of course.

=INDEX(A112,MATCH(F1,MID(A1:A12,9,LEN(F1)),0),4)
=INDEX(A112,MATCH(G1,RIGHT(A1:A12,LEN(G1)),0),4)

6. ## Re: VLookup With LEFT Function (2002 SP3)

Legare and Andrew, the MID() was what I was looking for and I was able to quickly get the info I needed. I knew I knew what it was, just couldn't remember :-(

Thanks.

7. ## Re: VLookup With LEFT Function (2002 SP3)

Tony and Ban, I am not familiar with Index so now I am going to study what you sent and see if I can use it elsewhere. I love learning new stuff especially in Excel.

Thanks for the input!

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•