“VLOOKUP”

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

As a first step I always select an empty cell in my worksheet.
To select the “VLOOKUP” function, go to the “Formulas” tab in the “Ribbon”, and click the downward pointing arrow next to “Lookup and reference.”

Choose “VLOOKUP” from the drop-down menu.
In the upper section of the dialog box that appears, we give a value that we want to find. This can be text, a number, or a cell reference.
In this case I give a cell reference in (F3), then I can look at different values by changing cell content.
In the next section, I give the range in which Excel should look, this is our entire table (A2: D400).
In the third section I give the number of the column from our range, this is in my case column C, so it is the 3th colom in the range.
And in the last input field I give the value “TRUE” or “FALSE”.
If we choose FALSE, it looks if the cell content match exactly.
If you choose TRUE or leave this field blank, Excel looks for the best possible values.

When everything is completed, click OK, Excel will return # N / A in the box where our formula is completed.
This is because Excel has recieved no information after searching. We specified a cell reference in the first field, F3.
If we enter a value in cell F3, an item number, Excel will gives us its price in cell G3. If we modify cell F3, the price will be adjusted in cell G3.

“HLOOKUP”

We use the “HLOOKUP” function to find data based on a predefined value in a row.
In this example, I calculate the commission.

All this information is found in the range B8: E9.

We begin with the selection of an empty cell, in this case C4, here I want to calculate the commission for Peter in January.
We can do with the function wizard, butt for now I will type the formula.

As always I start with the equals sign, and enter the function. Once we type H , Excel displays a list of all available functions 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 it is the sales for January for Peter. So click cell C3.

Type a coma (,) .
For the next argument we give the range in which the data should be sought.
Drag the mouse pointer over the range (B8: E9) and

type a coma (,).
For the following argument, we must enter the row number, this is the row number from our range, NOT the row number from our “Worksheet”.

Though our function is now completely filled, we must finish our formula even further.
If we now press the Enter key, we see as a result 5% in the cell C4.
And this is not the intention, I want to calculate the commission for Piet.

So I close the parentheses, type *, and then the cell C3, sales of Piet for the month of January.

If we want to copy this formula to the other months, we have to make the cell references of our range “absolute”.
If you do not know how to do this .
Double click the formula in the formula bar, select the cell B8 and click the F4 key on your keyboard.
This is the quickest way.
Do the same for the cell E9

Featured Courses

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