In this Microsoft Excel tutorial, we'll take a look at the construction of a basic LOOKUP function and how to use it to locate information in a table.

- slide 1 of 2
### Basics on Lookup Functions

In Microsoft Excel, lookup functions are used to find a value that is associated with another entry in a table. For example, if we have a table containing a list of countries and their respective populations, we can use a lookup formula to find the current population of Denmark (or any other country in the list). These types of functions are particularly useful when dealing with data that is continuously being updated or modified.

There are many types of lookup functions in Excel, but in this series, we’ll concentrate on the three basic ones – LOOKUP, VLOOKUP, and HLOOKUP – and explain how they differ. We'll begin with the LOOKUP function.

- slide 2 of 2
### The LOOKUP Function

The most general lookup function, aptly called LOOKUP, will search a single row or column for a lookup value and return a result from another specified row or column.

As an example, suppose that we have a table that contains information on the discount available for buying multiple licenses of a particular software product. (Click any image for a larger view.)

In this case, we’d like to be able to construct a function where customers can enter the number of licenses they wish to purchase and be told what discount would apply.

A LOOKUP function can be created using the syntax

*=LOOKUP(lookup_value, lookup_vector, result_vector)*where

**lookup_***value***lookup_vector**designates the row or column in which you want to look for the value and the**result_vector**is the row or column containing the information that you want to “look up” for the value.Before we go any further, it’s important to note some of the limitations of the LOOKUP function.

- In order for the LOOKUP function to work properly, the values in the
**lookup_vector**must be sorted and arranged in ascending order.

- If the function can’t find the exact
**lookup_value**in the**lookup_vector**, it will use the largest value in the**lookup_vector**that is smaller than the**lookup_value**. For instance, if the values in the**lookup_vector**are 1, 5, 10, 20, and 30, and the**lookup_value**is 9, the LOOKUP function will return the result associated with the value of 5.

- If all of the values in the
**lookup_vector**are greater than the**lookup_value**or if the**lookup_value**doesn’t make sense in respect to the data in your table, the LOOKUP function will not be able to return a result, and an error message will be displayed.

**How to Use the LOOKUP Function**Now, let’s return to our example and show how to construct a LOOKUP function that will give the applicable discount for purchasing a specified number of software licenses.

First, we’ll add a separate smaller table where customers can enter the number of licenses they wish to purchase.

Note that in this example, the number of licenses will be entered in cell E4. This cell will be our

**lookup_value**. We will be searching for this value in the column titled**Number of Licenses**, which contains values in cells A2 through A8. So, our**lookup_vector**will be A2:A8. We will want to return the corresponding percentage found in the**Discount**column, which contains values in cells B2 through B8. This will give us a**result_vector**of B2:B8. Putting these three items together, our LOOKUP function becomes:*=LOOKUP(E4, A2:A8, B2:B8)*The screenshot below shows how this would be entered into the appropriate cell in Excel.

With this function in place, you can test it by entering a value for the

**Number of Licenses**in cell E4.We'll look at more robust forms of lookup functions in Part 3 of this series, but first we'll show how these tools can be combined with the IFERROR function to make error messages more meaningful. Continue on to Part 2 of this series to find out more.

- In order for the LOOKUP function to work properly, the values in the