﻿ Excel Tutorial: Using Lookup Formulas - VLOOKUP, HLOOKUP & Exact Match

# theBLOG

## Excel Tutorial: Using Lookup Formulas

/ home / blog /Excel Tutorial: Using Lookup Formulas

Written by Andy Trainer– Mon 14 Apr 2014

This tutorial is an extract from our popular 1-day Advanced Excel course. Lookup formulas are one of the most asked about topics so we decided to put together a short tutorial.

Lookup tables provide a way of producing numbers or text that cannot be calculated with a formula. For example they could look up a salesman's commission dependent on what has been sold (Example 1), or the amount of discount available to a customer based on the amount of goods ordered (Example 2).

Excel has two Lookup functions; Vertical and Horizontal.

VLOOKUP looks down the vertical column on the left side of the table until the appropriate comparison is found.

HLOOKUP looks across the horizontal row at the top of a column until the appropriate comparison is found.

### Function Syntax

LOOKUP(lookup_value,table_array,col_index_num,range_lookup)

HLOOKUP(lookup_value,table_array,row_index_num,range_lookup)

## Example 1 - VLOOKUP Function

The formula would be written in the following way: =VLOOKUP(E8,Commission,3)

 Lookup Value In the example above, the Lookup Value would be E8 Table Array The range that contains the table and Lookup values - In the example above this would be B1:D4 or if the cells are named this can be used instead, i.e. Commission Col/Row Index Num Which column or row displays the result to be extracted, in the example above we are trying to extract the Percentage due, this is in Column 3 of the Lookup table Range Lookup This argument will allow for an exact match. Only used when looking for an exact figure or piece of text. (Not needed in the example above)

## Example 2 - HLOOKUP function =HLOOKUP(B9,Discount,3)

 Lookup Value In the example above, the Lookup Value would be B9 Table Array The range that contains the table and Lookup values - In the example above this would be A2:D4 or if the cells are named this can be used instead, i.e. Discount Col/Row Index Num Which column or row displays the result to be extracted, in the example above we are trying to extract the Percentage due, this is in Row 3 of the Lookup table. Range Lookup This argument will allow for an exact match. Only used when looking for an exact figure or piece of text. (Not needed in the example above)

## Example 3: Looking for an Exact Match

The following is a list of Personnel, in B14 we need to find out what Department Mr Dorfberg is in and in C14 his Salary. As we are looking for an exact piece of text, then a fourth part would be required in the VLOOKUP Statement. =VLOOKUP(A14,Personnel,4,FALSE)

 Lookup Value In the example above, the Lookup Value would be B14 Table Array The range that contains the table and lookup values - In the example above this would be A1:H11 or if the cells are named this can be used instead, i.e. Personnel Col/Row Index Num Which column or row displays the result to be extracted. In the example above we are trying to extract the Department for Mr Dorfberg, this is in Column 4 of the Lookup table. Range Lookup This argument will allow for an exact match. Therefore, we would need to enter the word FALSE or 0, as a last part of the Statement, to ensure the Form. TRUE or 1, would be used to say it is not an Exact Match.

Now you know how to use Lookup formulas, find out three of our top hidden gems in Excel.

Back