.png)
If you’ve never used vlookup, this may seem like a crazy statement, but if you dig through data for a living, Vlookup is what keeps you sane. It’s what lets you have dinner on time, and sleep at night. It is the single most time-saving function that I use on a daily basis. If you haven’t used it, or it never seems to work right, this article is designed for YOU. I want everyone to know the power of this tiny feature, and for it to be the magical miracle worker for everyone!
FASTER! Already get the gist but it doesn't seem to be working right? Jump down to the color-coded formula example to get a quick refresher.
First things first, if you haven’t cleaned your data, then do that first! Don’t know what I’m talking about? Read the first post in this series.
Now, with your data clean, let’s start making magic!
Here’s the scenario. You’ve received an email with a spreadsheet. That spreadsheet has a data dump from your database, but the data is spread across multiple tabs. No single tab has all the data in one place, and clicking through each tab stresses you out rather than informs you. You need a summary! So let's build one. We'll start simple: let’s summarize all of the leases to include basic acreage, acquisition and formation data.
We’re going to work with four tabs – The summary tab is what we’ll create, based on the Leasehold, Assignments, and Formations tabs.
First, let’s see what our tabs actually contain. For this example, we’ll keep it fairly basic, but vlookup doesn’t care how many tabs you have. It is a beast at finding data!
In the leasehold tab, we have all the data for each specific lease - that includes the lease number, names, dates, book/page numbers, and acreage. However, it doesn’t tell you where that lease came from, or what formations are covered. It also includes dozens of other columns of other data, such as provisions, NRI calculations, status codes, and lease notes. Since we are only looking for a summary of the data, we don’t need any of that last bunch right now.
(Don't worry, this is all totally randomized data. Acreage, names, dates, and STRs are all fairly nonsensical.)
In our other tabs, we have simple tables that match a formation or assigning party to a code that is then found in the Leasehold tab:
If you've ever dealt with the structure of a database, you know that you'll sometimes have countless tables, just like these. The code in column A ties back to a code listed in the Leasehold tab. If we don't merge the data, someone would have to flip between tabs to see the name of the assigning company or the formation for each lease.
We could also add the information to the Leasehold tab using the Vlookup function, but since we're making a simplified summary anyway, let's do that step once instead of twice and move it over to the Summary tab.
Before we start moving any data, figure out exactly what data you want to pull over to your summary. This can be changed later, but it's easier to get as much as possible the first time. To start, I'm going to include:
Lease Number
Lessor Lessee Acquisition Type
Acquisition Code Acquired From Section Township Range County State Royalty Net Acres Gross Acres Working Interest Net Revenue Interest Depth Code Depth Name
Next, you could copy over each column individually, or you can copy over one and pull the rest with a formula. We'll be using the formula. First we go to the Leasehold tab and copy over all of Column A:
Why copy one and then pull the rest with the formula? Good question. We're copying in the lease numbers. In this example, each lease number is only listed once, so it is a unique way to identify each record. When using Vlookup, you need a unique identifier so you know you're matching to the correct data. (For example: if I said, go get me the crayon, but didn't specify what color, I would probably be given the wrong crayon.)
With those in place, we can now use Vlookup to pull everything else. (FINALLY, right?)
As with any formula, there is syntax that works just like a sentence. For a familiar example, let's use the SUM formula. Everyone uses this formula to add columns of numbers and find the total. The sentence for the SUM formula is, "This cell equals the sum of some other cells (specifically, cells A1 through A21)" or =SUM(A1:A21).
For Vlookup, the sentence is, "Find this cell's data, in this other area. Once you've found it, show me what it says in that same row, in this other column - exact match only."
Or =VLOOKUP(A2,Leasehold!A:V,3,FALSE).
Let's break that apart to make it easier:
For our purposes, we're looking for the lease number in Cell A2. We want to find it on the Leasehold tab in Column A, and the data we want returned is somewhere between Columns A and V. For this field we want the 3rd column (Column C, which is Lessor Name on the Leasehold tab) and we want an exact match.
If you put "true" instead of "false" at the end, it would look for an approximate match to the lease number. Don't do that. You don't ever want that.
Finished result:
The formula has looked at the data in cell A2 on the summary tab, found the same data in Column A on the Leasehold tab, and returned what was found in the 3rd column of that same row - it found the lease number, and returned the Lessor name.
Seem like a lot of work for one field? But now all you have to do is double-click on that little black box in the bottom right corner of the Lessor's name, and it will auto-fill the other 4,000+ names.
And now that we have the formula in place, it's easy to copy over the other columns and make minor changes. However, you don't want to just copy/paste the cell itself, or the cell references will change. You want to copy/paste from the address bar. Once you've successfully copied over, you can then copy down, just like you did in Column B. See the video below for several examples (you may want to open full screen):
Notice that the only change I made in each formula was the column number. I want the formula to keep using the lease number, and just return different pieces of data in that same row - Lessee, Location, Acreage, etc.
You may have noticed that I clicked the buttons in the Excel ribbon - this was only for visual clarity. Feel free to use keyboard shortcuts like CTRL+C/CTRL+V and TAB to do the same tasks.
We've filled in the first few tabs, so you would only need to keep filling in those tabs to keep pulling information from the Leasehold tab. But what about when we pull Assignment and Formation information? You need to tweak the formula slightly.
To pull up the company that the lease was assigned from:
=VLOOKUP(E2,Assignments!A:B,2,FALSE)
(E2 is the cell with the ACQ Code, and "Assignments!" means it will look on the Assignments tab instead of the Leasehold tab.)
To pull the formations based on the Depth Code:
=VLOOKUP(Q2,Formations!A:B,2,FALSE)
(Q2 is the Depth Code, and "Formations!" means it will look in the Formations tab instead of the Leasehold tab.)
End Result?
A fully filled in Summary tab!
One last thing before you start formatting, moving, and emailing that spreadsheet - turn all of your formulas into standard text! This way, if someone deletes a column, it doesn't break all of your formulas!
Click on the square above row 1 and before column A (this will select the entire sheet), right click and select Copy. Then right-click in the SAME SPOT, and select the clipboard with the 123 (Paste Values). This will turn all of your formulas into standard text.
That is probably a lot to digest, but once you get the hang of it, it becomes second nature. For quick reference, just use the formula graphic above to remind yourself what each piece of information represents.
Next time - Macros!
Comments