• Skip to main content
  • Skip to header right navigation
  • Skip to site footer

My Online Training Hub

Learn Dashboards, Excel, Power BI, Power Query, Power Pivot

  • Courses
  • Pricing
    • SALE 20% Off All Courses
    • Free Courses
    • Power BI Course
    • Excel Power Query Course
    • Power Pivot and DAX Course
    • Excel Dashboard Course
    • Excel PivotTable Course – Quick Start
    • Advanced Excel Formulas Course
    • Excel Expert Advanced Excel Training
    • Excel Tables Course
    • Excel, Word, Outlook
    • Financial Modelling Course
    • Excel PivotTable Course
    • Excel for Customer Service Professionals
    • Multi-User Pricing
  • Resources
    • Free Downloads
    • Excel Functions Explained
    • Excel Formulas
    • Excel Add-ins
    • IF Function
      • Excel IF Statement Explained
      • Excel IF AND OR Functions
      • IF Formula Builder
    • Time & Dates in Excel
      • Excel Date & Time
      • Calculating Time in Excel
      • Excel Time Calculation Tricks
      • Excel Date and Time Formatting
    • Excel Keyboard Shortcuts
    • Excel Custom Number Format Guide
    • Pivot Tables Guide
    • VLOOKUP Guide
    • ALT Codes
    • Excel VBA & Macros
    • Excel User Forms
    • VBA String Functions
  • Members
    • Login
    • Logout
    • Password Reset
  • Blog
  • Excel Webinars
  • Excel Forum
    • Register as Forum Member

Excel BETWEEN Formula

You are here: Home / Excel Formulas / Excel BETWEEN Formula
Excel BETWEEN Formula
May 9, 2013 by Mynda Treacy

A little while ago Brendan asked me how he could check if values in a list fall between two limits.

Now, it’d be nice if there was a BETWEEN Function, but there isn’t. Hence why the title of this tutorial isn’t ‘BETWEEN Function’, it’s ‘BETWEEN Formula’.

I’ve got two options for you.

Watch the Video

Subscribe YouTube

Download Workbook

Enter your email address below to download the sample workbook.

By submitting your email address you agree that we can email you our Excel newsletter.
Please enter a valid email address.

Download the Excel Workbook and follow along. Note: This is a .xlsx file please ensure your browser doesn't change the file extension on download.

Excel BETWEEN formula using AND

In the example table below I’m checking if the value/date in column B falls within and inclusive of the upper and lower limits:

Excel BETWEEN Formula

In English, the formula in cell E5 simply reads: is the value in B5 greater than or equal to the value in cell C5 (the lower limit) AND less than or equal to the value in cell D5 (the upper limit)?

If you strictly want to check if a value/date falls between the upper and lower limits, you can omit the equals sign from the formulas like so:

Excel BETWEEN Formula

Excel BETWEEN formula using MEDIAN (Inclusive)

The MEDIAN function returns the middle value in a set of 3 numbers. If the value is between two values, then it will be returned by MEDIAN, which makes it perfect for this task.

Excel BETWEEN Formula

Taking the examples above, the formula in English reads: does the value in B23 equal the median of the value, lower limit, and upper limit? It’s inclusive of the upper and lower limits, as we can see by the results on rows 24 and 29.

If you prefer to exclude the upper and lower limits, you can add/subtract 1 to the lower and upper limits respectively, as shown below:

Excel BETWEEN Formula

Thanks

Thanks to Roberto for the MEDIAN solution.

Excel BETWEEN Formula

More Excel Formulas Posts

top excel functions for data analysts

Top Excel Functions for Data Analysts

Must know Excel Functions for Data Analysts and what functions you don’t have to waste time learning and why.
excel advanced formula environment

Excel Advanced Formula Environment

Excel Advanced Formula Environment is a long awaited, new improved way to write, name and store Excel formulas.
Pro Excel Formula Writing Tips

Pro Excel Formula Writing Tips

Must know Excel formula writing tips, tricks and tools to make you an Excel formula ninja, including a new formula editor.
excel shaping arrays

New Array Shaping Excel Functions

The Excel Shaping Array Functions makes it easier than ever to reshape arrays and ranges using these purpose built functions
excel nested if functions what not to do

Excel IF Formulas and What Not To Do

Excel IF formulas can get out of hand when you nest too many IFs. Not only do they become unwieldy they’re difficult for anyone to understand
excel image function

Excel IMAGE Function

The Excel IMAGE Function enables you to embed images in a cell using a formula. It supports BMP, JPG/JPEG, GIF, TIFF, PNG, ICO, and WEBP files

Excel VSTACK and HSTACK Functions

New Excel VSTACK and HSTACK functions makes combining arrays of cells easy and with some clever tricks we can extend their capabilities.
identify overlapping dates and times in excel

Identify overlapping dates and times in Excel

How to identify overlapping dates and times in Excel with a formula that checks a range of cells. Works with Dates and Times.
New Excel Text Functions

TEXTSPLIT, TEXTBEFORE and TEXTAFTER Functions

TEXTAFTER, TEXTBEFORE and TEXTSPLIT are exciting new Excel Text functions. They’re fairly self-explanatory, however TEXTSPLIT has some cool features.

Top 10 Intermediate Excel Functions

Take your Excel skills to the next level with this top 10 intermediate Excel functions. These are must know functions for all Excel users.




Category: Excel Formulas
Previous Post:Excel Tables as Source for Data Validation ListsExcel Tables as Source for Data Validation Lists
Next Post:VLOOKUP Multiple Values in Multiple ColumnsVLOOKUP Multiple Values in Multiple Columns

Reader Interactions

Comments

  1. Silahis Plucena

    May 1, 2018 at 5:11 pm

    I have this formula that doesn’t seem to work, appreciate if you can let me know how to go around with it 🙁

    =IF(EACP>100,”Red”,IF(EACP>9070<90,"Yellow",IF(EACP<70,"Green"))))

    Reply
    • Catalin Bombea

      May 2, 2018 at 7:10 pm

      Hi Silahis,
      Try this:
      =IF(EACP<70,"Green", IF(EACP<90,"Yellow", "Red"))
      for EACP of 90 and up will return red. If you want another level:
      =IF(EACP<70,"Green", IF(EACP<90,"Yellow", If(EACP<100, "Blue", "Red")))
      You should know that nested if's are evaluated until the next logical test is true, the remaining logical tests will not be evaluated.

      Reply
  2. David N

    July 4, 2017 at 5:52 am

    Your BETWEEN MEDIAN exclusive solution (at least the idea that it’s plus/minus 1 by default) only works with whole numbers. Say you have Value = 4.5, Upper = 5, and Lower = 2. Then using plus/minus 1 as the default ends up with MEDIAN(4.5,2+1,5-1) = MEDIAN(4.5,3,4) = 4 and returns FALSE instead of TRUE. Of course it could work with some other plus/minus shift as long as that shift was appreciably small compared to the size of any possible decimal fractions.

    In the end, the MAX MIN approach seems more manageable, particularly if you don’t know which number should serve as the upper/lower limits. Otherwise you would just use direct comparisons as Bryan described. But I don’t really see the point of including a negative sign in the MAX portion, and have to disagree that making it inclusive should be accomplished by increasing/decreasing the limits by 1 as opposed to simply tacking an “or equal to” onto one or both of the inequality signs.

    =AND(A3<=MAX(B3,C3),A3>=MIN(B3,C3))

    Reply
  3. Morton Wakeland

    May 11, 2015 at 3:33 am

    Mynda, it’s “ole Mort” in Dallas, Texas….Hey Happy Mother’s Day to you – let them spoil you today – smile.

    I have a Table of data on one worksheet – very simply as an example

    3 columns
    0 10 -20%
    11 20 -10%
    21 30 0
    31 40 +15%
    41 50 +20%

    I have a “value” on another worksheet in a Table row, say it is 36, I want to use MEDIAN to find that I should use a factor of +15%.

    Am also confused with referencing data in Tables on other worksheets and [@headername]
    Most often when I begin typing the reference header name it will appear with others in a list, now it’s not, the names I’m using are LowerValue and UpperValue.

    Thanks for any suggestions Mynda…
    Mort Wakeland, Dallas, TX

    Reply
    • Mynda Treacy

      May 11, 2015 at 8:14 pm

      Hi Mort,

      I’d use a VLOOKUP on a sorted list to return the factor or +15%. Here is an example:

      https://www.myonlinetraininghub.com/excel-2007-%e2%80%93-vlookup-sorted-list-explained

      Not sure what’s going on with your table references but have you tried using your mouse to select them instead of typing them in?

      Mynda
      P.S. had a lovely Mother’s day, thanks!

      Reply
    • Catalin Bombea

      May 11, 2015 at 10:29 pm

      Hi Morton,
      Hope you and Mynda don’t mind if i give you another option to solve your problem, in fact, i think you are already familiar to this solution:
      If the data you provided is in range A2:C6 (only data, no headers), then you can use this formula:
      =INDEX(C2:C6,MATCH(A10,A2:A6,1)) , where A10 holds the value to be searched (that 36 you mentioned).
      The ranges can be hard typed into the formula, like this:
      =INDEX({-0.2;-0.1;0;0.15;0.2},MATCH(A10,{0;11;21;31;41},1))
      The result is exactly the same, of course.
      But if you are looking for an interpolation, to give the precise percentage, even if your data table has a higher granularity, you can try the interpolation formula and a nice interpolation UDF i made as a student.
      The sample file can be downloaded from MOTH OneDrive Folder.
      Cheers,
      Catalin

      Reply
  4. Bryan

    July 1, 2013 at 11:20 pm

    Your first formula doens’t work if your upper limit and lower limit are both negative numbers, and the value you compare against is positive. For example, if Value = 3, Upper Limit = -2, and Lower Limit = -4, then the formula evaluates to: AND(3MIN(-4,-2) –> AND(3-4) –> AND(TRUE,TRUE) –> TRUE.

    Couldn’t you just use =AND(A3C3)?

    Reply
    • Mynda Treacy

      July 2, 2013 at 2:35 pm

      Hi Bryan,

      Thanks for your comment. Another good reason to use the MEDIAN method as opposed to the MAX and MIN option.

      I’m not sure what you mean by ‘just use =AND(A3,C3)’, where are you using them?

      Kind regards,

      Mynda.

      Reply
      • Bryan

        July 8, 2013 at 11:12 pm

        Sorry about that, it looks like it treated my greater than and lesser than signs as an HTML tag and removed them (some of my formula explanation is missing as well). The formula was supposed to be =AND(A3 {lesser than} B3,A3 {greater than} C3).

        Reply

Leave a Reply Cancel reply

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

Current ye@r *

Leave this field empty

Sidebar

More results...

Course Sale

mynda treacy microsoft mvpHi, I'm Mynda Treacy and I run MOTH with my husband, Phil. Through our blog, webinars, YouTube channel and courses we hope we can help you learn Excel, Power Pivot and DAX, Power Query, Power BI, and Excel Dashboards.

Subscribe to Our Newsletter

Receive weekly tutorials on Excel, Power Query, Power Pivot, Power BI and More.

We respect your email privacy

Guides and Resources

  • Excel Keyboard Shortcuts
  • Excel Functions
  • Excel Formulas
  • Excel Custom Number Formatting
  • ALT Codes
  • Pivot Tables
  • VLOOKUP
  • VBA
  • Excel Userforms
  • Free Downloads

239 Excel Keyboard Shortcuts

Download Free PDF

Free Webinars

Excel Dashboards Webinar

Watch our free webinars and learn to create Interactive Dashboard Reports in Excel or Power BI

Click Here to Watch Now

Blog Categories

  • Excel
  • Excel Charts
  • Excel Dashboard
  • Excel Formulas
  • Excel PivotTables
  • Excel Shortcuts
  • Excel VBA
  • General Tips
  • Online Training
  • Outlook
  • Power Apps
  • Power Automate
  • Power BI
  • Power Pivot
  • Power Query
trustpilot excellent rating
 

Company

  • About My Online Training Hub
  • Disclosure Statement
  • Frequently Asked Questions
  • Guarantee
  • Privacy Policy
  • Terms & Conditions
  • Testimonials
  • Become an Affiliate

Support

  • Contact
  • Forum
  • Helpdesk – For Technical Issues

Copyright © 2023 · My Online Training Hub · All Rights Reserved

Microsoft and the Microsoft Office logo are trademarks or registered trademarks of Microsoft Corporation in the United States and/or other countries. Product names, logos, brands, and other trademarks featured or referred to within this website are the property of their respective trademark holders.