Excel Course: Using a Database

The goal of this lesson is to provide you with an example of how to use data from a database.

In this example, our objective will be to enter a client's address into the bill using a drop-down list.

exercise objective - excel billing database

We recommend that you do this exercise as you work through the following lesson : click here to download the exercise.

Start by inserting the VLOOKUP function at the location of the name and enter the required parameters (see image below) :

Lookup_value : I2 : the cell that contains the client number to search for.

Table_array : DB!A:E : select columns A to E of sheet "DB" (note : if you fill out the columns only, rather than the cells, you can enter your database later and you won't have to change any of the formulas).

Col_index_num : the column number entered here is the column number in Table_array. If we enter 2, we will get the name, 3, the first name, 4, the address, and 5, the city or town.

Range_lookup : FALSE : find an exact match.

vlookup - excel billing database

The name of client number 1 is displayed.

vlookup name - excel billing database

Copy the formula to the following locations (without changing it) :

copy - excel billing database

Now edit the No_index_col parameter to get all the information.

data - excel billing database

How to concatenate multiple values

In this intermediate example, A1 and B1 each contain a word. The goal is to return the two words in A2.

example concatenate - excel billing database

To concatenate (= assemble) multiple values, use the & sign.

In this case, we would like to get : A1 + a space + B1, which gives us :

assembled cell values - excel billing database

The 2 words have been returned in A2 correctly and separated by a space.

concatenation result - excel billing database

Getting the last and first names on our bill, separated by a space, would be exactly the same with only one difference, which is that A1 and B1 would be replaced by formulas :

concatenate vlookup - excel billing database