< Previous lesson | Overview | Next lesson >
=VLOOKUP()
How to look up values in a grid
=VLOOKUP() is certainly one of the most powerful formulas in Calc. Simply put, you will use =VLOOKUP() to search for information in the leftmost column in an are you choose and return the value in another column on the very same line.
Everybody who works will large lists and use this formula, will never, ever go back to the "old ways". Personally I've worked with lists of several tens of thousand lines.
Create a list as shown here. Start with the headings in A1 and B1, and the data just below. It is good practice to always have a header in the columns, and keep the headers at the very first line of the spreadsheet, as it makes everything more tidy which, in turn, makes the table more predictable for formulas.
Go to cell D3 and enter "Name:". In D4 you enter "Amount:".
First we'll enter the formula and see the result, then we'll take a closer look at what's happening.
Go to E4 (the cell immediately to the right where you entered "Amount:", and enter: =VLOOKUP(E3;A1:B100;2;0)
NB! As you can see here, we're using a semi-kolon; ";", to separate inside the formula, your computer may be set up to use comma instead, ",". If so, correct the formula accordingly, everything else should be just the same.
OK, nothing interesting has happened so far, but you haven't done anything wrong! Now you can go to the next step.
Now, go to E3 (the cell immediately to the right where you entered "Name:", and enter: "Peter". When you now press [Enter] you should see the number "12" in cell E4, where you typed the formula.
What happened here is that the formula tried to find the name you gave just above in the table to the left. If Calc finds the correct name, it's instructed to give you the corresponding amount.
Try experimenting with typing different names, also try entering wrong names, just to see what happens.
OK, now we'll start analyzing this formula. It looks ugly, but it really isn't that horrible once we get to break it up a bit.
Take another look at it: =VLOOKUP(E3;A1:B100;2;0)
Now we'll break it up: =VLOOKUP( what to search for ; where to search ; what result to give ; accuracy )
what to search for
This can either be a value or, as here, a cell reference. In our example we wanted to search for something related to "Peter", and we got that that value from cell E3. We would have excactly the same result if we replaced E3 with Peter, like this: =VLOOKUP("Peter";A1:B100;2;0)
where to search
This is the cell reference to the table we want to work with. In this case it is the area A1 to B100. As you see, the names are given in the left column, and the =VLOOKUP() formula will always search in the left column of any table. This is important.
The reason for typing B100, which is obviously too far down, is to make sure that if we add more data here, it will be included in the formula. Data enteret in cell A101 and B101 will not be considered by the formula. It is, by the way, not required that you include the column headings, like I’ve done here.
what result to give
If Calc is able to find what you wanted to search for, where you wanted to search for it, you need to specify exactly what information you want in return. In this case, we specified that we wanted the related information from column number 2 in the table. Notice that the number is totally unrelated to the cell reference itself -- of the same table was placed in the columns F and G, the reference number for the feedback would still be number 2, as it refers to the table you defined in the green area.
accuracy
OK, to be more accurate it really means sorted or not sorted... If it says "1", it means that the list is sorted, and will give you the closest match to what you searched for. You will probably never use that in you entire life, so I suggest that you consequently use "0" instead, which requires a perfect match. If it doesn't find a perfect match, it will show an error message: "#N/A".
You can also use the words "true" or "false" instead of, respectively, "1" or "0", with the exact same result -- but requires more typing! Sometimes less i more :-) Then it would look like this: =VLOOKUP(E3;A1:B100;2;false) Same - same, but more to type.
I hope that you now can see some of the potensial of this formula. Especially if you think BIG -- imagine having a huge list of 30 000 entries that you want to search! That is not unrealistic, I've done it houndreds of times myself.
As always, it is important to make sure that you lock the cell references by adding "$" to the cell references if you intend to copy the formula, for instance like this: =VLOOKUP($E$3;$A$1:$B$100;2;0) This way, if you copy the formula to the right, both the value you want to search for and the area in which you want to search will remain unchanged.
We will give you more examples of how to use =VLOOKUP() later.
A final advise; it is wise to define an area much longer than what you need at the moment to make sure that you can accommodate more lines of data (the green part of the formula). If you ad more data to your table, it will automatically be included in the area to search in the formula. If you use a limited area, you will need to update the formula manually.
First an example of what happens if you make it fit only the amount of data that you already have:
...then an example of what happens if you make it a bit wider:
