### VLOOKUP example

Look at the table below. How can we find matching product names for the codes on the right (1003, 1010, 1020 & 1018)?

1.

**Select the cell**in which you want to display the product name and click the

**fx button**under the ribbon. Search for the

**VLOOKUP**(vertical lookup) function.

Why VLOOKUP? We need to use vertical lookup because the product codes are in the same column, so we are searching codes from up to bottom. If the product codes were in the same row, we would use HLOOKUP.

2. To the

3. To the

4.

5. In to

6. To get the Price + VAT for the product, we again

7. Col_index_num is 5 this time.

8. Write false to Range lookup and hit OK. Drag copy your results down to get the following.

If you want to do it manually, here is the syntax:

Get the name of the product:

Get the price + VAT:

Horizontal lookup function (HLOOKUP) works exactly the same, it just searches from left to right.

To find which project we have to work on Thursday, use the following function:

This would return us 3 % as 500 is the closest match on the top row.

**lookup_value**choose the product code.3. To the

**Table_array**choose**both columns A and B**, because column A holds the product code and column B holds the corresponding name.4.

**Column_index_num**we put the number of the column that holds the information we are looking for. In this case it is number 2, because of the selected are the second column has the product name.5. In to

**Range_lookup**we write FALSE, because we want to find exact match for our product code. If we don't have an exact match for our code, we could search for the closest match by typing TRUE.6. To get the Price + VAT for the product, we again

**use the product code to find the corresponding price**. This time need to**select all of the columns**in between column A and column B.You cannot skip columns.7. Col_index_num is 5 this time.

8. Write false to Range lookup and hit OK. Drag copy your results down to get the following.

If you want to do it manually, here is the syntax:

Get the name of the product:

=VLOOKUP(G2;A:B;2;FALSE)

**Note: depending on your regional settings, you might have to use comma (,) instead of semicolon (;) as a separator.**Get the price + VAT:

=VLOOKUP(G2;A:E;5;FALSE)

**Note: depending on your regional settings, you might have to use comma (,) instead of semicolon (;) as a separator.**

**Lookup value is G2****Table array is A:E****Column index number is 5****Range lookup is false, as we want the exact match**

### HLOOKUP example

To find which project we have to work on Thursday, use the following function:

=HLOOKUP("Thursday";1:2;2;FALSE)This will give us Project D.

**Note: depending on your regional settings, you might have to use comma (,) instead of semicolon (;) as a separator.****Lookup value is "Thursday"****Table array are rows 1:2****Column index number is 2****Range lookup is false, as we want the exact match**

### Range lookup Example

Range lookup parameter can be used to find the exact match for the lookup value OR a closest value from the top row. So as an example, we have a shop that offers discount to clients that buy more stuff (look at image below). If a customer bought things for total cost of 541 euros and we wanted to find out which discount category we belong in (look at image below):

=HLOOKUP(541;1:2;2;TRUE)

This would return us 3 % as 500 is the closest match on the top row.

## No comments:

## Post a Comment