One of our top 10 skills that separate experienced users from new users is lookups.
With lookups, you can access information from a list or database and use it for analysis or to help build another list.
To follow along, do the exercise in the Lookup spreadsheet. To just see the final result, pull up the Lookup Result sheet.
This will use the same database we used in the sorting exercise. You may recall there we have a number in the product field.
We will use the lookup feature to add another column for the name of the product. We added another table with a list of the product numbers and names.
Note that we set up a named range for the list- product. This is a good practice to simplify your lookups. Here is a key thing on this source table you will use for your lookups. The first column should be a field that used for the lookup. Since it will be the product number in this case, we made it the first field. Otherwise, the lookup will not work.
Now we move to the table we want to populate. We add a field for the name of the product. Then we go the formula line, click on the "fx" function icon, and pull up the vlookup function. It is called vlookup since the is a vertical lookup looking across columns.
There are four "arguments" to set up for the lookup:
Lookup value- this is the value in the current list we want to look up. In this case, it is cell F2 from the Product column
Table array- this is the source table where we want to lookup the information. Since we gave it a named range before, this step is easier- we insert the name- product.
Column index number- this is the column in the source table where we will find the information we want. This is the trickiest part of setting up a lookup, especially if you are dealing with a wide table. You need to remember the column number. However, if you happen to forget, just put in any column number. It is an easy change. You can go back and just change the column number in the vlookup formula.
Range lookup- this is optional, but we always recommend using this. This is a true/false argument- false if you want an exact match, which we almost always enter. True if you want an approximate match. You can get some strange results if you select true or leave it blank. Be safe and use false.
Then we click OK and see our result. Sporting goods now appears.
We can then copy the formula down to fill in the list.
A plus of using lookup tables is that it can be easier to maintain your information. Suppose product category 50 changes from magazines to newspapers. That can be an easy change by going to the product table and replacing magazines with newspapers. The data database would be automatically updated.
This is particularly powerful if you use the same table across multiple spreadsheets. In this case, we could place the product table in a separate worksheet. Then we can still do the lookup to complete the data database in this example. Suppose later we had a separate file with invoices from another year. We could link to the same separate product table.
So based upon the above paragraph, if you know you might need to reference the same table of information across multiple spreadsheets, we suggest setting up a table like product in its own separate file. Then when you have changes such as to the name of the product in this example, all you have to do is update the one spreadsheet that contains the product table. All the other spreadsheets that reference this table will be updated.
One cleanup point. In our example, we added a column to the right to pick up the product name. Don't forget to change the named range called data to include this column to the right.
Another cleanup point. In this case, the lookup formula across thousands of records will increase the size of the file. If you don't think you will need the lookup again or are comfortable with recreating the lookup formula when you need it again, then you could copy the result and convert it to values. In this example, we would copy the name column, then do a paste special, values from the edit menu. The result would be a smaller file.
Lookups are a very powerful way to complete fields needed for lists and to help you analyze data. They also can make it much easier to maintain data.
Next we will cover how to deal with a common error message that you can get with lookups.