1.16 VLOOKUP Function

The VLOOKUP function in Excel is a powerful tool that is used frequently in accounting contexts. This function examines a table full of data, looking for the value that you identify, and returns a corresponding piece of data. For example, assume you are given the item number and unit price for many products in a table such as in the image below. You are preparing an invoice where you input the total quantity, the item number and description, and you want to add the unit price in order to calculate a total. You have the option of manually searching the item number out from among the list to identify the unit price. Alternatively, you could use the VLOOKUP function to have Excel do the work for you.

Notice the function first tells Excel which cell you are trying to find. cell G5 is the item number 94620 in your invoice. The one for which you are trying to identify the unit cost. The next part of the VLOOKUP function tells Excel where the table is that contains the item numbers with corresponding unit cost - where Excel needs to look to find the unit cost for this item number. The third part of the VLOOKUP function tells Excel which column in the table you are looking for. In this case, you want the Unit Cost, which is column 2 in the table, so you put the number 2. The last part of the VLOOKUP function tells Excel whether you are looking for an exact match or not. Ther command FALSE tells Excel you are looking for an exact match. Generally, you will want to find an exact match so the item ID in your invoice and the item ID in the table are exactly the same. This command will automatically return the dollar amount of $7.38 as the Unit Cost for this invoice.