An easy step-by-step-manual to help you in using Vlookup in Excel
Excel’s Vertical Lookup formula, or Vlookup, can be a great and efficient tool. It combines various data records from different Excel reports clearly and neatly into exactly the data you need. You need to know Vlookup if you want to efficiently make the reports you need!
However… Vlookup is not the kindest of formulas around. If you do not fill it in exactly as needed, your result will give you #N/A’s and nothing but #N/A’s (Excel’s error message). Except for maybe an additional headache, because you cannot get done what you want.
I work in a company with very many reports, very many products and thus many item numbers. Unfortunately, the data I need is almost never available automatically in one report. I therefore use Vlookup at least every working day and often multiple times per day. This makes me an excellent guide to help you with Excel’s Lookup.
First you can watch this video as a tutorial to see Vlookup being used. More details, tips and tricks can be found in the rest of this blog!
For this example I have created two simple files: one with item numbers and customer prices.
This is the table with customer prices:
And this is the table with cost prices:
Because we want to calculate the margin, we need the cost price added to the first file.
I am working in this example with different files, therefore I write ‘file(s)’. However you can apply the same principle with data on different worksheets of the same file as well. So you can also read ‘worksheet(s)’ where I use the word ‘ file(s)’
Before I knew Vlookup, I would copy the first item number and then go to the other file. I would then find the item number there, copy the corresponding cost price and paste back in the original table. Doing this per cell for many cells is very inefficient. Vlookup is the easier and efficient solution.
By using this formula, you actually tell Excel: Find the cell I now select (in this file) as well somewhere in the first column of the other file. Please then show me here as a result the value that is in the xth column from the left; from the same line as the selected cell!
And THIS is how you do that:
The very first thing to do, is check if all cells involved in the Vlookup are set as numbers. Check the cell format / cell properties of your original table, your other table and the column in which your results will show. If any cell involved is not formatted a number, Vlookup won’t work. If you see anywhere in your table a green triangle in the upper corner of the cell; Vlookup won’t work. Select all these cells, click the exclamation mark and select ‘mark as number’. After this, even doublecheck the format of the cell to make 100% sure it is really a number in the cell-formatting part of Excel as well.
You can check this by selecting your cells, right mouse click, select Format and make sure you see this:
If all this is ok; then you are ready to insert the formula. Decide where you want to add the data, place your cursor there and click. In our case; C2 is the logical location to begin. Don’t forget to check if this newly to be added column C is formatted as number as well. If not, Vlookup won’t work. Then choose Formulas-Insert Formulas-Vlookup:
When you click “Insert function” again in the right bottom corner, the following window appears (on a Mac on the right side, on a Windows machine in the middle of the screen):
You have to fill in four lines in the following way:
- Lookup_value: click in this field and select one cell. This one cell you select has to be located in the same sheet as the Vlookup formula, and this cell has to be in the other file as well. If not; obviously Excel can not combine the two. You can select this cell from any column in this file. In our example, the shared value between the two sheets is the item number. Therefore, we select the item number in A2. But if the item numbers would have been in column C, D, E, we would select C2, D2 or E2, and the Vlookup would still work.
This common value (in our case the item number) has to be present in the FIRST column of the OTHER file. It can be located in any line of this first column, but it has to be in the FIRST colum. If not, the Vlookup will not work.
So don’t mix the two up:
In the current file, the shared value (in our case item number) can be in any column you wish (because you can select any cell you wish in the first field). In the other file this item has to be in column A!
- Table-Array: click in this field to make sure the next selection ends up indeed in this second field. If you do not do this, the first field (Lookup_value) will be replaced by what you select here.
You start your selection completely to the left of the other file with column A. Make sure that you include at least the one that holds the values you want to see in the file with your Vlookup formula. In our example, I select columns A to C, as the cost price is in column C. If the cost price would have been in column D or BY or column…., you would select the first column until at least column D, BY, or ….
Make the selection by clicking on the column header A, hold your left mouse button clicked, and move to the right until you have selected all necessary columns.
- Col_index_num: click in this field to make sure the next selection ends up indeed in this second line. If you do not do this, the second field Table-Array) will be replaced by what you select here.
In this field, you fill one number: if the desired value (the cost price) is in column B; here you type a 2. If it is in Z; you type a 26, etc, etc. You can imagine that it is already smart to build up your other file in such a way that the wanted value is far to the left. In our example, the costprice is in Column C, so we type a 3.
- Range_lookup: this is the easiest part of the formula, don’t even spend a second thinking about it. Just fill in the number 0.
In our example, the result in the formula builder looks like this:
Then you press enter, or click ‘Done’, and you have the first result!
All you have to do now is copy the formula in C2 and copy to all cells below and you are done! This can be done by copy and paste; or faster by moving your cursor above C2 until you see a plus, then doubleclick, and Excel will copy until the bottom of your table. You see as well how the information in the formula builder is transformed by Excel in the formula (in the blue circle below).
Important piece of advice:
If you are satisfied with the result of your Vlookup, then please don’t forget to remove the formula and replace by the resulting values. You can do this by selecting the column, choose Copy – Paste special (in the same location) – Values. If you do not do this, and you further process one of the two files involved in the formula by deleting, moving or adding columns; the outcome of the Vlookup will change!
Frequent Error Checklist:
Unfortunately it happens quite often, that your efforts do not give the expected results. If this is the case, follow this checklist for common problems and how to solve them:
- Are you 100% sure that the cells involved in the formula all are formatted as numbers?
- Are you 100% sure that the results you expected are really in ‘the other file’? Check this by copying one or a few of the cells in your one file, and see if you can locate them in the other file. If you don’t find them, they are simply not there for Excel to find. If you do find them, continuing searching for other problems.
- Problem: In the four fields that you have to fill in, you mix up where you need to fill in only a cell and where complete columns. Don’t forget: the first field is one cell and the second field is a selection of complete columns starting with column A
- Problem: In the four fields that you have to fill in, you mix up where you need to fill in data from the same file, and where you need to fill in data from the other cell. Keep in mind: the first field is from the same file as is the formula and the second field needs to be filled with data from the other file.
- Is the lookup_value (first field out of four in the formula builder) really in the first column of the other file? Not in column B when column A is hidden, or in a completely different column? If it is not in column A, move it so it becomes column A in the other file and start building your formula anew.
- Occasionally, I get stuck in the second field of the formula builder when I work with two different files and there is just too many columns or too many rows in the other file, Excel just refuses to go on. Just abort, first copy only the data you need to a different worksheet of the file in which you are inserting your Vlookup, and start again. Don’t forget to check if after finishing and replacing the formula by hard numbers, if you maybe want to remove that particular worksheet again.
If all this fails, then I am at the moment also not sure what you have been messing around… In that case: give me a call, send me the file, and I will help you with the problems that Excel VLookup is causing you.
Or of course you can hire me to build your Excel reporting for you, as this is what I am good at and what I do (among others) for a living. Then you can be assured it gets done properly and you can get back to your own non-frustrating tasks!!
Do you have any other Excel issues that you could use a hand with? Please don’t hesitate to let me know, it would make me very happy to help you!