VLookUp - Maintaining Links

On the StockList sheet, you need to include the ISBN Numbers of the books listed on the BookList sheet. Creating simple links as on the previous page may at first glance seem adequate, but it is important that the reference between the book title and its ISBN number is never changed. Therefore, it is necessary to enter a function into the spreadsheet which says, "Find this book title and give me its ISBN number". This is done using the VLookup function.

To make VLookUp work correctly, it is important to define a named range of cells containing the data to be looked up.

Select the BookList sheet and highlight the cells containing all the book titles and ISBN numbers. Now select Insert from the menu, move across Name and select Define...; in the Define Name dialogue box which appears, enter the name title_isbn or similar (single words only, please!), checking that the correct cell references have been referred to at the bottom of the box. Click on OK then save the file. Note that any block of cells can be named in this way and referred to by the defined name.

Now return to the StockList sheet and select the cell to the right of the first book title. Press the = key (or Select the = icon) to enter the formula mode, then select the function pull-down menu which is to the left and select VLookUp from the list. (If VLookUp is not in the list, Select More functions... and locate it under the category Lookup & Reference.) Whichever method you chose, a dialogue box should appear on the screen into which you need to enter the details which the function needs to work.

Take a deep breath before starting this section! The VLookUp function needs a minimum of three pieces of information for it to work correctly. The first of these is the information you want it to find (or match); in this case, this is the book title which is in the cell to the left of the current cell. To refer to this cell, select the cell chooser icon () and then Select the cell with the mouse pointer; Select the cell chooser icon again or press Enter on the keyboard to return to the dialogue box (check that the correct cell has been entered - if it hasn't, repeat the process). The second piece of information you need to enter, is the range of cells containing the data to be matched - this will be the block of cells you named title_isbn (or similar) earlier. To enter this, either enter title_isbn (or whatever you called the block of cells), or Select the cell chooser icon and highlight the cells containing all the book titles and ISBN numbers (press Enter or Select the cell chooser icon to return to the dialogue box). The third piece of information is to enter the number of columns to the right of the Title column (this is counted as column 1) you want data returned; in this case the next column on the right, so enter 2. Optionally, you can include a fourth piece of information, which is to choose whether Excel makes an exact match or a close match. I recommend you enter False here (which, strangely, means an exact match!). The image below shows the VLookUp dialogue box with the information entered and the cell chooser icons highlighted.

If it helps, click the image above to see another, step-by-step explanation.

To check everything works correctly, resort the BookList data and ensure that the StockList data has mirrored the changes. If all is well, save the file.