Excel "In Between"

This Excel example will show you how to determine if a cell value is "In Between" a pre-established range. Please see the details below for setup steps and a visual example...

Example Assumptions:

• The spreadsheet has been named: Range Example
• This cell value that you wish to calculate is: A8
• The 'in between' values to check against are placed in cells: B4:L4

Setup Steps:

1. Create your "in between" values somewhere on the spreadsheet (Cells B4:L4 are used in this example)
2. Create a "Named Range". In this example, the Named Range has been called: "SPREAD".
3. In the "Refers to:" field for "SPREAD", enter the following reference: ='Range Example'!$B$4:$L$4
4. Click 'OK' to save the Named Range.
5. Enter the formula below into a cell that's (preferably) adjacent to the cell that you wish to find the range for:

=INDEX(SPREAD,-SUMPRODUCT(-(-(LEFT(SPREAD,FIND(" ",SPREAD)-1))>=(-A8))))

The formula above uses the "SPREAD" Named Range, which you defined earlier, to determine if the referenced cell is 'in between' the pre-established range.

For a visual representation of how to set this up, please see the screen shot below...