Excel Help - Lookup function?

[Alex]

Would you be willing/able to email the spreadsheet you are having the problems with and I'd look to see if I could see the issue?

[Betty]

Originally Posted by Alex

Would you be willing/able to email the spreadsheet you are having the problems with and I'd look to see if I could see the issue?

Are you kidding me? I would LOVE to email you this file and get your help. Thank you for being my Excel hero!

[Betty]

Tried to send it using your profile info but it says you have preferred not to receive emails through that feature. I'll PM you my email address.

[Alex]

Email it to alex@mouseplanet.com

[Alex]

I'll email back the spreadsheet with some tweaks I made. Fortunately for me the issue is really really simple (at least the problem with it breaking at row 182).

Remember where I said to make sure your array left plenty of room for the size of the Address worksheet? So in my sample formula I used C1000 and D1000? The problem is, your Address worksheet is 14,428 rows long, so the attempt to match is failing because it is only searching in the first 1,000 rows and Row 182 just happens to be where you are alphabetically far enough along to be outside that 1,000 row range.


So changing C1000 and D1000 to C15000 and D15000 (or even larger) will get rid of that problem.

As you say, you have some data quality problems. The source of the 0 values in many rows is, as I'm sure you figured out, that not every name on the Address worksheet has a city or ZIP code associated with it. If you want to handle those a bit more gracefully you can replace the above formulas with these:

For City:
=IF(VLOOKUP(A2,Address!$A$1:$C$15000,3,FALSE)="","NO CITY",VLOOKUP(A2,Address!$A$1:$C$15000,3,FALSE))

For ZIP:
=IF(VLOOKUP(A2,Address!$A$1:$D$20000,4,FALSE)="","NO ZIP",VLOOKUP(A2,Address!$A$1:$D$20000,4,FALSE))

This will have the impact of showing "NO CITY" or "NO ZIP" instead of "0". Apply a little conditional formatting to the column (look under Format --> Conditional Formatting and you can set up a rule, for example, that will cause the text to display as red and bold when it says NO CITY) and they'll really pop if you need to in some way resolve them.

The source of the vast majority of your #N/A is that there simply isn't a match for the name on the Address worksheet. Some are because there really is no match, most are because "Aggasid, Michelle & Frank" on Order is "Aggasid, Michelle &". Nothing can be done to help with these except manually changing one to match the other. A small portion of them seem to be issues in text formatting such as "Wethers, Mark" on one and "wethers, mark" on the other. That could be accounted for in the formulas but they seem to be such a small portion it probably isn't worth the effort.

If you want to handle the #N/A errors more gracefully you can take the above revisions another step and use these:

For City:
=IF(ISERROR(VLOOKUP(A2,Address!$A$1:$C$20000,3,FALSE)),"NO MATCH",IF(VLOOKUP(A2,Address!$A$1:$C$20000,3,FALSE)="","NO CITY",VLOOKUP(A2,Address!$A$1:$C$20000,3,FALSE)))

For ZIP:
=IF(ISERROR(VLOOKUP(A2,Address!$A$1:$D$15000,4,FALSE)),"NO MATCH",IF(VLOOKUP(A2,Address!$A$1:$D$15000,4,FALSE)="","NO ZIP",VLOOKUP(A2,Address!$A$1:$D$15000,4,FALSE)))

This will result in any #N/A displaying as NO MATCH and any match with no city or ZIP displaying as NO CITY or NO ZIP as appropriate.

One issue, though, is that VLOOKUP in a large array is slow so you may see some lag in updating when/if you change values.

I'll email you the spreadsheet with all of these changes so you can see them.

[Betty]

Doh! I totally should have figured that out.

I just discovered the data integriety issues this morning and will be correcting those with some address correction software. I believe when we had to do some exports and imports when installing a new version of the (stupid) software that it caused much of that.

And the lack of people matching? The (stupid) software again.

That, I'm afraid, is going to end up making this nearly unusable if they can't even get the program to export properly.

(Can you tell my love of the inventory software we use?)