Results 1 to 4 of 4
  1. #1
    3 Star Lounger
    Join Date
    Jun 2005
    Posts
    388
    Thanks
    3
    Thanked 0 Times in 0 Posts
    I have a table where the Lookup value has an apostrophe " ' " and spaces before the word. The array may have only spaces, no spaces-just the word, etc. How can i correct this by formula without retyping each criteria or array key? I am attaching a sample of the problem.

    Any one have some insight to overcome this problem?

    Thank you

  2. #2
    Platinum Lounger
    Join Date
    Feb 2002
    Location
    A Magic Forest in Deepest, Darkest Kent
    Posts
    5,681
    Thanks
    0
    Thanked 1 Time in 1 Post
    If there were no spaces on the right of some of the values you could use:

    =VLOOKUP(TRIM("*"&D2&"*"),$A$2:$B$8,2,FALSE)

    Else you can use it in conjunction with the IF function


    =IF(ISNA(VLOOKUP(TRIM("*"&D2&"*"),$A$2:$B$8,2,FALS E)),VLOOKUP(TRIM("*"&D2),$A$2:$B$8,2,FALSE),VLOOKU P(TRIM("*"&D2&"*"),$A$2:$B$8,2,FALSE))
    Jerry

  3. #3
    3 Star Lounger
    Join Date
    Jun 2005
    Posts
    388
    Thanks
    3
    Thanked 0 Times in 0 Posts
    Thanks but what has to be done if, maybe, there are spaces on the right of the value in the cell.

    without taking up to much of your time, could you define the IF function vlookup for my own knowledge base, I would appreciate it?

  4. #4
    Platinum Lounger
    Join Date
    Feb 2002
    Location
    A Magic Forest in Deepest, Darkest Kent
    Posts
    5,681
    Thanks
    0
    Thanked 1 Time in 1 Post
    [quote name='MNN' post='770615' date='15-Apr-2009 15:12']Thanks but what has to be done if, maybe, there are spaces on the right of the value in the cell.

    without taking up to much of your time, could you define the IF function vlookup for my own knowledge base, I would appreciate it?[/quote]

    Have a look at the attached
    Jerry

Posting Permissions

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