Lesson#257: Excel Solution for Searching or Lookup Values Between a Range of Values

Searching for a value in Excel is easy when you know the exact match. But what if you need to look up a value that falls within a range — for example, checking whether a date falls between a start and end date, or finding a tax rate based on an income range?

This is a common scenario in payroll systems, date validations, grade classification, or price slabs. In this article, we’ll explore practical Excel formulas to efficiently handle “lookup between a range” tasks.

1. Understanding the Challenge

Unlike VLOOKUP’s exact match, “between” lookups require checking two boundaries — a lower limit and an upper limit.
For example:

Start Date	End Date	Status
01-Jan-2025	15-Jan-2025	Holiday
16-Jan-2025	31-Jan-2025	Working
01-Feb-2025	10-Feb-2025	Project A

If we have a date like 18-Jan-2025, it’s not an exact match to any cell, but it clearly falls between 16-Jan and 31-Jan. Our goal is to detect or retrieve the corresponding status.

2. Method 1 – TRUE/FALSE Check with SUMPRODUCT

If you only want to know whether a given date or number is in any range, use:

=SUMPRODUCT((A2>=B2:B100)*(A2<=C2:C100))>0

How It Works:

(A2>=B2:B100) → Checks if the value in A2 is greater than or equal to the start values.
(A2<=C2:C100) → Checks if the value in A2 is less than or equal to the end values.
Multiplying the two creates an array of 1s (TRUE) and 0s (FALSE).
SUMPRODUCT counts matches. If greater than zero, the value is inside a range.

Result:

TRUE → The value is in a range.
FALSE → The value is not in any range.

3. Method 2 – Return the Matching Result Using INDEX + MATCH

If you want the actual corresponding value (e.g., the status for the date), try:

=INDEX(D2:D100, MATCH(1, (A2>=B2:B100)*(A2<=C2:C100), 0))

Important: This is an array formula in older Excel versions (press Ctrl+Shift+Enter). In Excel 365 / Excel 2021, it works directly.

Explanation:

(A2>=B2:B100)*(A2<=C2:C100) returns 1 where both conditions are true.
MATCH(1, …, 0) finds the first row where both conditions match.
INDEX(D2:D100, …) returns the corresponding value from the result column.

Example Output:
For 18-Jan-2025, Excel will return “Working”.

4. Method 3 – Lookup Between Ranges with XLOOKUP (Excel 365)

If you have Excel 365, XLOOKUP can simplify range lookups.
Assume your lower boundaries are sorted:

=XLOOKUP(A2, B2:B100, D2:D100, "Not Found", -1)

Parameters:

A2 → Lookup value.
B2:B100 → Start of ranges (must be sorted in ascending order).
D2:D100 → Return array.
"Not Found" → Default if no match.
-1 → Find the next smaller item if exact match isn’t found.

Note: This works best when ranges are continuous and non-overlapping.

5. Common Use Cases

Date range classification (project phases, leave periods, offer validity)
Grade assignment (marks falling between certain scores)
Tax or commission calculation (slabs)
Price tier lookup (discounts based on quantity purchased)

6. Tips for Range Lookup in Excel

Ensure start and end ranges are consistent and non-overlapping to avoid wrong matches.
Convert your data to an Excel Table for auto-expanding ranges.
When working with dates, format them consistently to avoid mismatches.
For large datasets, use helper columns to improve readability and performance.

Final Thoughts

Excel doesn’t have a direct “between lookup” function. Still, with formulas like SUMPRODUCT, INDEX + MATCH, or modern XLOOKUP, you can easily detect and retrieve values that fall within a specific range. Once you understand these methods, you can apply them to everything from payroll systems to sales analysis.