Excel Tutorial: Using Lookup Formulas

user Andy Trainer

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