Lookup Within Boundaries

3 Ways to Lookup within Boundaries: SUMIFS, SUMPRODUCT & INDEX

lookup excel hlookup within boundaries lookupThree powerful Excel Formulas that lookup values in ranges, i.e. ranges that include lower bound and upper bound values.

Method 1:

First method uses the SUMIFS formula with greater than and smaller than as criteria.

Method 2:

SUMPRODUCT formula works great here too as it can work well with exceptions (and condition)

Method 3:

The next method uses INDEX and Match function to find the category that coincides with the defined boundary values. Instead of looking for the exact match, we will use the option “less than” in the match type argument.

BONUS!

Method 4:

A fourth option that came to my mind when I was creating the thumbnail for the video was the LOOKUP formula:
=LOOKUP(B2,$H$2:$H$7,$G$2:$G$7)

Watch the steps in this video:

The new Excel Dashboards course is here!

Now available on Udemy

Visually Effective Excel Dashboards for your reports.
Redeem your coupon for 75% off below.

GET 75% OFF THE COURSE
Free Ebook

Leave A Comment

Share This