It will not always be possible or desirable to use a database system to summarize your data. Excel provides two formulas that can be useful in these cases.

Vlookup Function

Vlookup is useful for making Excel combine information from multiple tables. This sort of task is what databases are best at, but sometimes you just need to pull in information from a single column of a different table. See VLOOKUP FUNCTION for details and examples.

In our exercises, we will be adding patient information (age and sex) to a table of admission records. Both the admission records (our source array) and the patient information (our table array) contain a patient ID column. We will use that ID number to make the comparison.

The important things to remember are as follows:

• There must be a key to compare between the two tables. You specify where that key is in the source array with the “lookup value” argument.
• In the “table array”, the key must be in the first column
• You specify which column of the table array to look in to find the information you want to copy.
• You almost always want to specify “range lookup = F” to ensure that you get the correct match. If you don’t specify range lookup, or if you specify “range lookup = T”, Excel will do its best to find an approximate match. If you do want an approximate match, you must sort the table array in ascending order of the key value.
• If you want to copy the vlookup formula to more than one cell, you need to make sure that you lock in the table array by using $ signs as appropriate. For example, if we using the cells A2:C17 as our table array, and we wanted to copy the vlookup formula down a column, we would need to write the range as A$2:C$17, to prevent Excel’s default behaviour of incrementing the row number when copying a formula down a column.

Frequency Function

The frequency function is a useful way of summarizing data in Excel. It counts the number of occurrences of a particular set of values within a data range. See FREQUENCY function for an overview.

The function takes arguments “data array” and “bins array”. The bins are where you specify the *upper* end of the range of interest. The result of the function is the count of non-blank numeric cells in the data array, with values equal to or less than the specified bin value. In the example given at the link above, the bins array is the three numbers 70, 79, and 89. The frequency function will return *four* numbers: the count of elements in the data array that are equal to or less than 70; the count of values that are >70 but <= 79; the count of values that are >79 but <=89; and the count of values that are >89.

The other important thing to know about the frequency function is that it is an array function. That means that it needs to be entered in a special way: instead of hitting enter, you select all of the cells for the destination of the function output and hit control-shift-enter. See Guidelines and examples of array formulas for details.

Other Excel formulas that you can use for searching and counting are COUNTIF and SUMIF formulas.