Excel Help - Lookup function?

[Betty]

I could really use some help if someone is available and willing...

I've got 2 spreadsheets in excel - although they could be put into one on seperate pages or even on the same page.

One spreadsheet had sales order numbers, names, source of sale and amount of sale.

The other one is a name and address list.

I need to have the first spreadsheet have 2 more columns that use the city and zip code data from the other spreadsheet.

The result would be that I could check the source of each sale and see what city it was from, thereby letting me see which areas a magazine is doing well in and which zones from that magazine are not.

I've tried using the lookup function and can't get it to return the accurate city. I'm assuming the zip code will be easy and just copying the basic idea of the city column.

I would be matching up the two using the name column - which are exactly the same on each spreadsheet - although some customers will have more then one sales order.

If anyone can give me some pointers, I'd really appreciate it.

Thanks. :)

[BarTopDancer]

I suggest you delete both spreadsheets and go home.

[Betty]

LOL - well I AM actually leaving the office. But alas, when I return, they will be sitting here, staring at me, ready to make them work together.

They've been fighting me this afternoon and so I turn to you guys for help.

H....e....l.....puh. ;)

[Alex]

Is this a one time blend or do you need it to change on the fly?

And it'll be easier if you can put them in the same workbook as separate sheets. (Or, at least, easier for me to explain once I figure it out.)

[Alex]

Ok, here is doing it from two worksheets in the same workbook. It assumes that in Worksheet 2 (the address one) that each name is unique.

Assumptions for the fomula I'm about to give (so you know how to adjust for your reality

Worksheet 1 has been titled Order
Worksheet 2 has been titled Address

Layout of Order worksheet is:


A B C D
1 Name Order City ZIP
2 Bob Jones 2 couches
3 Caper Realty 4 lamps
4 U of Somewhere 163 desks


Layout of Address worksheet is:


A B C D
1 Name Address City ZIP
2 Bob Jones 123 Some St. Baltimore 98763
3 Ted Smith 1 Another St. Detroit 98798
4 Caper Realty 456 Some St. Ogden 97845-6544
5 Boeing Intl. 2 Another St. Chicago 43246-5468
6 U of Somewhere 789 Some St. Seattle 15498


So, to get column C of the Address worksheet into column C of the Order worksheet put this formula in cell C2:


=VLOOKUP(A2,Address!A1:C1000,3)


Then drag that formula down to each order row and it should work fine, resulting in Orders looking like this:


A B C D
1 Name Order City ZIP
2 Bob Jones 2 couches Baltimore
3 Caper Realty 4 lamps Ogden
4 U of Somewhere 163 desks Seattle


Repeat in column D using this formula:


=VLOOKUP(A2,Address!A1:D1000,4)


and you'll get the ZIP codes as well.


To explain what is happening in that formula:

=VLOOKUP - The VLOOKUP command finds a value in a specified column and returns the value from a specified cell in that row.

(A2, - This is the value you are trying to match. So for an order row, this is the name in the A column of this particular row.

Address! - This tells the formula that you'll be looking in a range on the Address worksheet

A1:C1000, - This is the cell range on the Address! you'll be using. It should cover the entire scope of the information on the Address worksheet. If you'll frequently be adding to this table then the lower-right corner definition (C1000 here) should be much lower than you'll likely ever use.

3) - This is the column that should be used in the row where the matching string is found. So if the match is found in row 163, it will return the value in cell C163. C is the third column.

[Kevy Baby]

Thank you Alex. Even though I wasn't the one who asked, this will still help me in my world. I had been wanting to learn the VLOOKUP command.

[Betty]

Thank You very VERY much! I will try it first thing Monday morning.

And again - Many, many thanks.

(and though I feel compelled to include a happy face or snapping fingers, I'll refrain.)

[Scrooge McSam]

Alex,

You did a beautiful job explaining how the VLOOKUP function works. I am in awe. Last time I had to explain this, I did nowhere near as good a job as you just did.

But when I was first reading your overview, something bothered me about it and I couldn't quite put my finger on it. I kept looking at this thread last night trying to figure out what it was. This morning it hit me.

There is just a tiny problem with your method, and it is this part...

Then drag that formula down to each order row and it should work fine

Unless you use absolute cell references for your array, Excel will adjust the array reference as you drag it down.

Rather than your formula...


=VLOOKUP(A2,Address!A1:C1000,3)


... I would suggest...


=VLOOKUP(A2,Address!$A$1:$C$1000,3)


I would also change your second formula to read...


=VLOOKUP(A2,Address!$A$1:$D$1000,4)


The absolute cell references will ensure Excel doesn't mangle your array reference as you drag the formula down.

Other than that one small detail, you're dead on.

:snap: Good job!:snap:

PS - I see I'm not the only one who leaves a little extra room in the array reference. That little trick saves a LOT of time if you regularly edit your array, which I'm constantly doing.

Also, I normally include "FALSE" as the fourth argument (optional) in this function to ensure I get exact matches (I'm normally doing this on numbers). For example...


=VLOOKUP(A2,Address!$A$1:$C$1000,3,FALSE)


For Betty's purposes though, it's probably not necessary.

[Alex]

Yes, I should have made the array reference absolute as you suggest. I always forget to do that until it breaks my results. My small test didn't break but in the real world it would have.

[Betty]

It works - sort of. sigh.

I used the example above that included the false statement as it was giving weird data that was corrected by that.

However, about 180 lines down, I start getting #N/A errors. I thought at first that the address sheet had a slightly different version of the name in column A as the program has been known to cut off names when exporting. And if fact, that did fix one or two lines of the error. However, at line 182, I go to nothing but #N/A's from there on down.

I went so far as to copy and paste the name from the one tab into the other just to verify that the name was exact. Made no change. If I remove the "false" notation from the end of the formula, I get "Ladera Ranch" down the page instead of the n/a error. Ladera Ranch is a city on the spreadhsheet but is not the city mentioned when the error starts.

ARGH!

I love excel - but hate the program I'm using to do the export which is full of little sniggly issues that come up when I least expect them.

[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]

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,FAL SE)),"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,FAL SE)),"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?)