Results 1 to 7 of 7
  1. #1
    4 Star Lounger
    Join Date
    Jan 2001
    Location
    Michigan, USA
    Posts
    408
    Thanks
    0
    Thanked 0 Times in 0 Posts

    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
    <font face="Comic Sans MS"><big><font color=4682b4>Sherry</font color=4682b4></big></font face=comic>

  2. #2
    Gold Lounger
    Join Date
    Feb 2001
    Location
    Dublin, Ireland, Republic of
    Posts
    2,697
    Thanks
    1
    Thanked 0 Times in 0 Posts

    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. #3
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    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.
    Legare Coleman

  4. #4
    Platinum Lounger
    Join Date
    Jan 2001
    Posts
    3,788
    Thanks
    0
    Thanked 1 Time in 1 Post

    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. #5
    3 Star Lounger
    Join Date
    Jan 2001
    Location
    Ankeny, Iowa, USA
    Posts
    298
    Thanks
    0
    Thanked 0 Times in 0 Posts

    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. #6
    4 Star Lounger
    Join Date
    Jan 2001
    Location
    Michigan, USA
    Posts
    408
    Thanks
    0
    Thanked 0 Times in 0 Posts

    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.
    <font face="Comic Sans MS"><big><font color=4682b4>Sherry</font color=4682b4></big></font face=comic>

  7. #7
    4 Star Lounger
    Join Date
    Jan 2001
    Location
    Michigan, USA
    Posts
    408
    Thanks
    0
    Thanked 0 Times in 0 Posts

    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!
    <font face="Comic Sans MS"><big><font color=4682b4>Sherry</font color=4682b4></big></font face=comic>

Posting Permissions

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