For training call +44(0)1273 6222 72

Your basket is empty View Cart shopping cart

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:

excel vlookup tutorial

=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

excel hlookup tutorial

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

excel vlookup exact match

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

Posted under:

Back
blog comments powered by Disqus

New Online Courses  

  +44(0)1273 6222 72 Call now to enquire