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.

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

Good job!

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.

[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.)

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