Go Back

Lookup value between two numbers

Generic FormulaLook 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.

 

Did this post not answer your question? Get a solution from connecting with the expert.

Another blog reader asked this question today on Excelchat:
Here are some problems that our users have asked and received explanations on

I'm trying to input a value into an IF statement where if the value is between two numbers, a certain value should appear. How do I do this?
Solved by A. E. in 18 mins
Need help tying out numbers between two tabs using sumifs formulas. I already have the formulas in place they are just equaling the numbers between tabs
Solved by E. E. in 23 mins
Need help using a sumifs formula to tie numbers out between two tabs
Solved by G. C. in 24 mins

Leave a Comment

avatar