“VLOOKUP”

We use the “VLOOKUP” function to find data based on a predefined value in a column. In this example, we will look for the price of a particular article.

As always, we first select an empty cell in my “Worksheet”. Next. we add the “VLOOKUP” function by selecting the “Formulas” tab in the “Ribbon” and by clicking the downward pointing arrow next to the “Lookup & Reference” button.

Choose “VLOOKUP” from the drop-down menu.

In the upper section of the dialog box that appears, we identify the value we are looking for. This can be text, a number, or a cell reference.

In this case I choose a cell reference (F4) allowing me to look for different values by changing the cell content.

In the next section I define the “Range” in which “Excel” should look, in this case our entire table (A5: E23).

In the third section I enter the number of the column from our “Range”, in my case column D, therefore nubmer 4.

And in the last box I give the value “TRUE” or “FALSE” in.

When we choose ” FALSE”, the cell contents must match exactly.

If you choose TRUE or leave this box blank, “Excel” looks for the closest match.

When everything is completed and we click OK, “Excel” will return ‘# N/A’, in the box where our “Formula” is completed.

This is because “Excel” has not received any information as to what it has to search for. We have a cell reference specified in the first box, F4.

When we enter a value of an item number in cell F4, “Excel” will give us this price in cell G4. If we modify the item number in cell F4, the price will be adjusted in cell G4.

 

“HLOOKUP”

We use the “HLOOKUP” function to find data based on a predefined value in a row.

In this example, we will calculate commission based on sales.
All this information is found in the “Range” C10: F11 (1).

We begin first with an empty cell and select it, in this case C6 (2). Here I will calculate the commission for Tom in January.

You can of course do this using the wizard, but on this occasion I will type in the “Formula”.

 

As always I start with the equals sign, and enter the function.
Once we have typed ‘H’ ,” Excel” displays a list of all our available features, starting with an H.
Double-click the “HLOOKUP” function. This places the function in the cell.

The first argument that we must enter is the search value. In this case, this is for the sale in January for Tom. So click cell C5.

Type a comma(,) Now you are ready for the next argument.

For the next argument we give the “Range” in which the data is stored

Drag the mouse pointer over the “Range” (C10: F11) and type a comma (,) before you enter the next argument.

For this argument, we must enter the row number from our “Range”, NOT the row number from our “Worksheet”.

 

Although our function is now completed, we still must finish our “Formula”. Should we now press the Enter key, we would see 5% as the result in cell C6.

This is not our intention as we want to calculate how much commission Tom gets.

In order to do this we close the parentheses, type * and C5, calculating the commission based on sales Tom realized for the month of January.

If we want to copy this “Formula” to the other months, we make the cell references of our range “Absolute”.
In case you do not know how to do this … Double-click the “Formula” in the “Formula bar”, select cell C10 and click the F4 key on your keyboard. This is the quickest way.

I leave calculating Pieter’s commission up to you….

Featured Courses

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