Lookup a value between two values in Excel

This came in handy for a project with two datasets, one had values at a quarterly basis and the other on a monthly basis. The monthly dataset needed to look up the quarterly values and turn them into monthly values based on the number of days in the month divided by the number of days in the quarter.

For VLOOKUP you need an exact value to look up from and it doesn’t work too well. With MATCH, you can change the match type (less than, equal to or greater than) and don’t need an exact value. Though you need to do quite a few nested formulas to do conditional logic using INDEX and MATCH together.

The solution is to create a matrix the columns being the value or the date that needs to be looked up and the rows the test of where the look up value falls between.

This way you can have a formula to look at any number of conditions.

In Excel, you can do calculations on boolean values TRUE = 1, FALSE = 0. Applying this to conditions means that the lookup value can be brought in if the condition is TRUE.

Summing up the column will bring in the only true value in the row. And you have your lookup.

This was what was used for a date range but any number of conditions can be applied. The solution is general enough for multiple use cases.

Leave a comment

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