For training
call *+44(0)1273 622272 *

/ 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.

LOOKUP(lookup_value,table_array,col_index_num,range_lookup)

HLOOKUP(lookup_value,table_array,row_index_num,range_lookup)

*The formula would be written in the following way:*

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) |

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) |

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.

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.