Vlookup not working on some but works on few.

[SomethingUnreal]

Hey EHF!

OK so I am doing a vlookup from sheet2 to match Iowa (2) sheet tax codes. I used Vlookup, as you will see in column F sheet2 to grab data from sheet called iowa 2, why are some erroring out and others working? Anyone know a work around?

The forum didnt let me attach it so her eis a external link to view the xl file.

Downlaod

http://microunified.com/demo/Zcodes.xls

Thank you for taking the time to help!

[alansidman]

In your example. Your look up value is Belle Plaine with a space between the two words. In you lookup table it is spelled without the space. Excel views these as two separate items.

In the case of Bloomfield, in your lookup table there is an extra space after the name. Again, excel views these as two separate items. Your look up data must be exactly the same to return what you need.

I only looked at the two items. If there are more items, then I suspect that there are similar issues of spacing.

[Pete_UK]

Nothing seemed to happen when I clicked your link (got the message "Downloading from microunified.com", but after a couple of minutes I gave up).

Can you post your formula here? I suspect that you have not used absolute addresses in your table, so that when you copy it down the addresses change.

Hope this helps.

Pete

[SomethingUnreal]

Try right click and save as and download that way. Is there a way to tell vlookup to ignore the spaces? My formula is =VLOOKUP(C284,'Iowa (2)'!$A$5:$E$1367,4,FALSE)

[alansidman]

You will have to run a clean or trim on the ones with extra spaces like Boone. For issues like Belle Plaine, you will have to manually clean them up. This is can be an irritant when the data comes from two different sources and was entered differently.

For the clean/trim, you may want to create a simple macro that runs down the entire column for each sheet and does a clean/trim.

Alan

[Norie]

BellePlaine is all one word on Iowa(2).

Bloomfield has a space at the end on Iowa(2), as do Boone, Burlington, Carroll, Cedar Rapids...

So basically the data in column C on Sheet2 isn't matching the data in column A on Iowa(2) because of things like that.

[SomethingUnreal]

Yeah def it was the spaces for sure, thanks for telling me, had no idea.

[Pete_UK]

You could do this to ignore the spaces in C284:

=VLOOKUP(SUBSTITUTE(C284," ",""),'Iowa (2)'!$A$5:$E$1367,4,FALSE)

but that assumes that you do not have spaces in column A of the Iowa (2) sheet - you can remove those using Find & Replace (CTRL-H) in one operation.

Hope this helps.

Pete

[SomethingUnreal]

OK cool, well hey thanks everyone for the quick and prompt responses. I learned something new. hanks again for all of your guys time and effort. I really appreciate it!

[SomethingUnreal]

You could do this to ignore the spaces in C284:
=VLOOKUP(SUBSTITUTE(C284," ",""),'Iowa (2)'!$A$5:$E$1367,4,FALSE)
Pete

I will give this a shot, thank you!!