Retrieving the first value in a list that is greater / smaller than a specified value

pasted image 0 (71)

The generic formula for finding the first number from a list that is greater than a given number is.

=INDEX(list,match(TRUE,list>number,0))

Example:

Here, I have created a list of goods and their prices. The price list is in range B2:B10. Now we want to find the first Price that is greater than 2154 ( a random number) in cell E2.

pasted image 0 (72)

Enter this Formula in cell E2 and hit CTRL+SHIFT+ENTER (**necessary to convert your formula into array formula) simultaneously:

{=INDEX($B$2:$B$10,MATCH(TRUE,$B$2:$B$10>D2,0))}

pasted image 0

We got our answer. It’s 3000. Now, whenever you change the value in D2 you will have a changed value in E2 (of course if matches to other criteria).

Explanation:

Every Formula works inside out. Most of the inner functions are compiled first and then outer.So let’s take a look at each segment of the formula.MATCH(TRUE,$B$2:$B$10>D2,0)

Match function returns the index of a value in a list.

The basic syntax of Match is:

MATCH(lookup_value,lookup_array,[match_type])

1. In our case, the lookup_value is TRUE. (why?) and

2. Lookup_array is ,$B$2:$B$10>D2. This will return an array of boolean values where the condition is matched. If you select this section and press the f9 key, you will see this.

{FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;TRUE;TRUE;TRUE}. That is why our lookup value is ‘TRUE’. It will return the index number of first true value and that is 7.

3. Match_Type is 0, 0 is for an exact match.

INDEX($B$2:$B$10,MATCH(TRUE,$B$2:$B$10>D2,0))

Index only returns a value from a list of given Index.

The basic syntax of Index is:

INDEX (array,row_number)

  1. In our case, the array is $B$2:$B$10. This is the list from which we want to get our results.
  2. Row_number is evaluated using MATCH(TRUE,$B$2:$B$10>D2,0). Which gives us 7.

pasted image 0 (73)

Hence the final match gives index row number 7. And using that INDEX returns the value at index 7.

Similarly, if you want to find the first number in a list that is less than the given value, just replace ‘<’ with ‘>’ in the formula.

Formula to find the first number in a list that is less than the given value

Just change the logical operator to < from >. It’s done. You have the first smallest number in the list.

{=INDEX($B$2:$B$10,MATCH(TRUE,$B$2:$B$10<D2,0))}

Now you know how to find the first larger/smaller value in a list. I hope it was resourceful for you. Use the comment section if it wasn’t and post your query.

Related Articles:

Vlookup Top 5 Values with Duplicate Values Using INDEX-MATCH in Excel

How to Retrieve Latest Price in Excel

How to Get Last Value In Column

How to Get Position of First Partial Match

Popular Articles:

50 Excel Shortcut to Increase Your Productivity

The VLOOKUP Function in Excel

COUNTIF in Excel 2016

How to Use SUMIF Function in Excel

 

 

Users are saying about us...

  1. “Select the cell, press and simultaneously press .” to be read as “Select the cell, press f2 and simultaneously press Ctrl+Shft+Enter .”” generates the accolades around a formula to turn them into an array formula.

    The tip was most helpful, keep forgetting how excellent the array function is.

  2. I have to create a sheet showing a sales reps revenue by client over a 15 month period with out showing the clients with out any revenue. I have no idea how to do this. Please help.

    • Hi Joe,

      You can simply create a snapshot and can add revenue for those clients who have given you revenue. For more info, we recommend you to login on http://www.excelforum.com and post your query there to get instant and clear solution for your query.

      Thanks,
      Site Admin

  3. Unless I’m mistaken (entirely possible):
    Does the formula find the number *just below* the criteria number or does it in fact find the *lowest* number in the column?

    EX: Criteria=81
    Greater Than=95
    Smaller Than=54

    Shouldn’t “Smaller Than” be 70?

    Thanks for providing these formulae and answer !

  4. Thank you for posting this. How do you write the function if you want to find the first instance bottom-up instead of top-down? The examples above only seem to be top-down in the array.

    Thanks!

  5. hello. Can you please help me on this one? i need to have a formula that will pick the nearest number that is less than a number and get it from row 1a – 4b.
    for example, that value is 3000 and choose 2a, the answer should be 2500

    1a – 1.00 – 417.00
    2a 50.00 1.00 2,083.00 2,500.00
    1b 75.00 1.00 3,125.00 3,542.00
    2b 100.00 1.00 4,167.00 4,583.00
    3b 125.00 1.00 5,208.00 5,625.00
    4b 150.00 1.00 6,250.00 6,667.00

    Hope to get a response. thank you so much in advance!!

  6. David Atherton

    No use if data is not sorted into order first. I am trying to find how many days from a given date/price that the price has gone up (made a new high). Prices go up/down every day. All these Excel functions seems to need the data in order.

Leave a Reply

Your email address will not be published. Required fields are marked *

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>

Terms and Conditions of use

The applications/code on this site are distributed as is and without warranties or liability. In no event shall the owner of the copyrights, or the authors of the applications/code be liable for any loss of profit, any problems or any damage resulting from the use or evaluation of the applications/code.

Visit Us On TwitterVisit Us On FacebookVisit Us On Google PlusVisit Us On Youtube