Tools You Should Be Using: Microsoft Excel
There are some very powerful functions in Excel that few people ever use: the Database Functions. If you have a table of information from which you’d like to ‘get’ a value, or maybe even if you’d like to add together or average a few values that meet the same criteria, they’ll let you do exactly that.
First, get a table of information, like the one below. It doesn’t have to have a ‘unique identifier’, unlike VLOOKUP. If it has multiple columns that you can use to identify the information, like ‘Department’ and ‘Division’ and ‘Category’, then you can actually use all those columns in combination with each other.
Next, you’ll need to set up your criteria. On your spreadsheet, you need to provide the column that you’re using, and the criteria itself. In the example below, if you want to ‘Get’ one record, you’re going to look through the SalesPerson, Customer, and Quarter columns so that we can be assured we’ll only return one value. There should be only one match for the sale Richardson made to Prince Paper in the first quarter.
Then, open the Insert Function dialog box, and look for the Database category of functions. The one you want is the DGET.
DGET needs to know where the table is (cells A6:F38), the column that has the values in it (F6, the ‘Commission’ column), and the criteria you set up to return the value (I6:K7, the SalesPerson, Customer, and Quarter labels, and the values that are under the labels).
DSUM is another popular request – the ability to add up all the sales that meet your critera. Your criteria needs fewer columns, because you’re not just trying to return one value, you’re getting a lot of them and adding them together. Choose the DSUM function from the same Insert Function dialog box.
Then, use the same table range for the first input, use E6 to specify the ‘Sales’ column, and the criteria you’ve put on your worksheet.
As you can see, they return the values from the table very easily, and if you change the criteria, your value returned changes. This is a great tool for retrieving business data from large tables of information.
If you’d like experience with the VLOOKUP, HLOOKUP, and Database functions, try either our Excel 2003 or Excel 2007 courses. For more cool functions you should be using, keep checking back with us on the blog.
