List VLOOKUP

Now that we know how to set a validation list, it’s a small effort to fill Excel cells with the other data from our client tab.

We do this through the vertical search function, or the VLOOKUP function code.

The first thing we do is select the range in which Excel should go looking for a name. It is always easier to look for something that has a name, right?

Select the worksheet “customers” (1) and select all columns from which you want to get data (2), which in this case are column A to column E. Next enter the range name “customer” (3) in the name box. Why do I select all columns? I can continue to add customers later, if necessary, and I do not need to change anything else.

Now select the worksheet “invoice” again and you select the cell where you want the address (1). Type the equal sign (=) and type “V”. Excel 2007 and 2010 immediately give you the possible functions in a frame (2). (In previous versions, just keep typing.) Double click the “VLOOKUP” (3).

Now the VLOOKUP function will appear in the cell and opens the parenthesis where you must enter four values.

The first is the search value. That is the value Excel goes looking for. I think this is clear that it is the parent cell, the cell containing the name of the customer. That is D5. You can select this cell or type. Then click the comma key on your keyboard (,).

Next we enter the matrix table. This is the table where it should go to get the information. As I mentioned in the beginning of this lesson, if we had given a name to this table, you only need to click the F3 key on your keyboard, Excel opens and a list of all range names in your spreadsheet. Select the range name and click ok.

Click again the comma key on your keyboard (,), Next you must type in the column range where this information must be found in Excel. This was the second column, the address column, so you type “2” followed by a comma (,). The last value we need to enter is whether or not it should be an exact match. In this case, of course, an exact match. For an exact match type a zero (0). If not, type (1).

Click the Enter key on your keyboard, and you’ll see that when a customer is chosen in the list, the address is automatically filled.

Now we apply this formula to the cells below and they will be adjusted when we select a customer.

The quickest way is to copy it.

The first thing you should do is to create an absolute cell reference of the formula for cell D5. This must always remain the same cell, so we make this $D$5

Then click and drag the fill handle down.

This gives the same result for each cell. Obviously, because the same value is found in the second column.

Now you have to change the value for the column index for each cell. It will be 3 for the “postcode”. (A). For the “Town” it is 4. (B). And for the “Tel#” it is 5. (C)

Now you are ready. We recommend you test everything at least once.

Featured Courses

• Downloading iPad Apps
• Lesson 1: Introduction to Publisher
• Lesson 65: File Formats