# Thread: VLOOKUP LOOKUP VALUE HAS AN ' AND SPACES & ARRAY MAY OR MAY NOT

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

3. 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. [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

#### Posting Permissions

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