Hello. I use vlookup formulas but sometimes the reports that are generated have spaces before or after words because the people that generate them are idiots. How do I do a vlookup that ignores the spaces. For example, if I want to lookup "Dogs" and I keep this as my lookup value but the value in the report is "Dogs__" with the line indicating a spaces. Obrigado.
Use the trim function...
Example: =VLOOKUP(TRIM(A1),$F$1:$G$17,2,0) >> A1 is the lookup value of dogs against the table in F1:G17
HTH
Regards, Jeff
Ok, but the lookup value is constant. The report is what is changing, so this trim doesn't helpIs there another formula?
Another weird thing I noticed is that if you have a space before the value it works, but not if you have spaces after...Why is that?
How about...
=VLOOKUP(A1&"*",$F$1:$G$17,2,0)
Last edited by jeffreybrown; 07-10-2013 at 07:24 PM. Reason: Posted the wrong formula...Sorry :(
Isn't that the same thing? The A1 does not need to be trimmed. I'm looking in the range (which is where the format changes), so that is the value that needs to be trimmed but that seems difficult.
Last edited by amartino44; 07-10-2013 at 07:24 PM.
Sorry, posted the wrong formula...
Check the update in #5
That worked. Why did that work haha? What is the "*"?
Not sure if I have an answer for why Trim did not work, but the "*" simply means, look for the lookup word and it doesn't matter what after it.
that stands for
A1 + anything after wards
some other useful wildcards found here
http://office.microsoft.com/en-au/ex...005203612.aspx
If you are satisfied with the solution(s) provided, please mark your thread as Solved.
Select Thread Tools-> Mark thread as Solved. To undo, select Thread Tools-> Mark thread as Unsolved.
trim didnt work because the lookup didnt have the extra spaces the values in the array/range did
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks