VLOOKUP ITEM (XP & 2003)

[MNN]

The attached has a vlookup formula that for some reason only is working for a few lookups. Could you help me on this? I cannot find the problem nor the solution.

Thank You

[LegareColeman]

Some of the strings in both column A and Column F have a trailing space. The VLOOKUP is only going to work when they both either have the space of don't have the space.

[Jezza]

Hi There

There is a bit of data cleansing you require for your data, I used the trim formula and then cut and paste specialled. Once I cleaned up all the dodgy codes I used this function in B4 =INDEX(myRange,MATCH(A4,myCodes,FALSE),2) and the copied down

I always use named ranges in workbooks with long list as it helps with resources.

Have a look at the attached cleansed wb

[HansV]

This seems to be the same problem as in the threads starting at <post:=602,723>post 602,723</post:> and at <post:=601,572>post 601,572</post:>. Same cause too. And same remark about the subject - again.

[MNN]

Thanks for the info.

one more question.What does the phrase mean at the end of your reply. (Latin i think)

Thanks again

[MNN]

Thanks

[Jezza]

>What does the phrase mean at the end of your reply

All my quotes are listed in my Control Panel as a FAQ. It is latin and it means:


In the good old days, children like you were left to perish on windswept crags.

[petermoran]

Hi,

Another way of addressing this problem without the chore of having to "clean" the data is to modify the Lookup_Value so that it is more flexible. In the first VLookup change the A4 to "*"&A4&"*" and then copy down and the problem is solved!

Good Luck!

Peter Moran

[Jezza]

Peter

That is an excellent point, I have seen other similar answers but using TRIM for MNN's problem. The secret is being aware that there is a problem with rogue characters in the data in the first place and your idea is a good contingency.