This is actually one of the most frustrating things I see on a daily basis. People spend time manually merging two datasets together or doing other stupid stuff like sorting and praying everything matches up. It pisses me off for 2 reasons:

1. There's a better way and it's absurdly easy, assuming you'd just get off your ass and learn something about the software that's been on your desktop for most of your career.

2. You should know number 1.

Anyway, a friend sent me a URGENT URGENT !!!!! OPEN IMMEDIATELY email asking for help with something like this, and I was just appalled by the jackassery, hence the post.

So, the vlookup is one of those desert island functions - one of those ones that I simply can't live without. On the most remedial level, take data with a key from one area and put it in another with the same key. So here's a sample:

So obviously, you've got a list of PO's with he prices of what was sold. You've got another list with the PO's and the invoice number. You want a list with the PO, the price, and the invoice number. You could either spend the next 10 minutes doing copy/paste. Depending on your data set size add in some cntrl+f and you could be there all day. Or you could break out the vlookup.

1. Name the range where you're getting data from. In this case it's G1:H13. If you're going to drag down the vlookup, you'll end up having to make it absolute so $G$13:$H$13 which is ok, but can be accomplished a little easier by......naming the damn range.

First select the cells in on the right hand side. Then go up to where it shows your current location on the sheet. Click on it, type a name you want.

2. Go in the invoice column. We're going to put together the formula. Basic syntax:

=vlookup(lookup what?,lookup where?,how many columns over should I get?, can I guess?)

In this case:

=vlookup(a2,insertnamehere,2,false)

Drag it down and.....

Vlookup has a brother that works much the same way. Hlookup: except you would do that if your data is transposed and going across instead of down. The syntax is exactly the same save for "how many columns over should I get?" becomes "how many cells down should I get?".

What else can you do with it?

Tons. Vlookup is one of the more powerful gadgets you have when it comes to manipulating data mainly because it lets you merge multiple lists/data sets together and quickly recreate datasets based on variables. It lets you do a lot of that stuff on the fly too, which can have some really cool properties for your own needs and for your deliverable.

Despite the fact that it's easy to do, you still have to exercise a little caution with it:

1. Excel will return the first matching value it finds. So, in our example if you have multiple invoices associated with a single po, you're only going to get the first one. You can quickly figure it out with a little count if action in a 4th column.

2. These really don't play well with copy and paste. You have to copy/paste special to put it some place else. In fact, if you're doing a quick vlookup merging for somebody, it's probably best to copy/paste special> values into another worksheet just to eliminate the chance that somebody will screw it up and blame it on your excel voodoo.

3. More of a usage note instead of a caution: vlookup can only return what's anywhere to the right what you're looking up.

So if you've got fields:

Purchase Order Sales Order Invoice

And you're looking up by sales order, you can only return what's in the invoice column. On the other hand, if you're looking up by purchase order you can return both the Sales Order and the Invoice.

4. As long as you leave the end false, you won't get a false positive.