**Generic Formula****Look Up Value Between Two Numbers**

`=LOOKUP(B4,minimums,results)`

**Explanation**

The **LOOKUP function** can be used to lookup the value between two numbers and deliver a commensurate result. To do this, the LOOKUP function and a sorted table will need to be used.

`=LOOKUP(lookup_value,lookup_vector,result_vector)`

Where:

**Lookup_value**is the value that will be searched – which can either be a number, logical value or TRUE or FALSE, or a reference to a cell. (Required)**Lookup_vector**is the data to be searched. Usually, it is one-dimensional and must be sorted in ascending order. (Required)**Result_vector**is the list from which the value will be returned. It is also one-dimensional and must always be the same length as the lookup_vector (when supplied).

**How this formula works**

When looking for the LOOKUP value between two numbers, the function performs a rough lookup in one range (lookup_value) and returns the value that corresponds in another range (result_vector).

To better understand this, let’s take a look at an example

**Example**

*Figure 1. Lookup value between two numbers*

The table above can be used to better understand the **Lookup function**. In our example above:

- Each value in the column labeled
**“Input”**represents a**lookup_value.** - Each value in the column labeled
**“Minimum”**represents the**lookup_vector.** - Each value in the column labeled
**“Result”**represents the**result_vector**

For our example, we used only the minimum values since LOOKUP automatically matches the next small value once it can’t find a match for the value it is searching. So, if we were to rewrite our formula to match our example, it will be given as

`=LOOKUP(B4,E4:E8,G4:G8)`

**Notes**

When looking up values between two numbers using the LOOKUP function, note the following:

- When an exact match of the lookup_value is matched in the lookup_vector, a commensurate value in the result_vector is returned.
- In the absence of a match, LOOKUP traverses the lookup vector until it finds a larger value, then it moves back to the previous row and returns a result.
- When the lookup_value is greater than the largest lookup_vector value, LOOKUP returns a result linked to the last lookup_vector value.
- Lookup_vector values
**must**be sorted in ascending order.

## Leave a Comment