Whether you’re using VLOOKUP or XLOOKUP, one limitation you’re going to face with those functions is that you can’t search within a range or use multiple criteria in your lookups. For example, suppose you’re looking at a stock’s history and wanted to know the last time it fell within a certain price range. You wouldn’t be able to do that with the aforementioned functions. But there is a way to accomplish that, using either MAXIFs or MINIFs. Here’s how.
Using MAXIFs and MINIFs as a lookup
With the MAXIFs and MINIFs functions, you are extracting either the smallest or largest data point in a range. And since you can apply multiple IF statements within these functions, you have the possibility to use multiple criteria. In the following example I have a list of Alphabet’s historical stock price going back multiple years:
Let’s suppose I wanted to find the last time that the stock was trading between $70 and $80. This is how the formula would look, assuming the date is in column A and the closing price is in column B:
=MAXIFS(A:A,B:B,”>=”&startprice,B:B,”<=”&endprice)
In column B, I have two criteria, one to check if the value is greater than or equal to the startprice variable ($70), and another to see if the value is less than or equal to the endprice variable ($80). Whenever that criteria is met, the value from column A is returned. And since the function is taking the maximum of those values, it will return the latest date in column A (i.e. the most recent, or the one closest to today’s date). If the date values were sorted in descending order rather than ascending order as they are above, then I would use the MINIFS function to get the same result.
Using the formula, it tells me that the last time Alphabet’s stock price was between $70 and $80 was on Oct. 29, 2020. And when looking at the range, it’s evident that looks to be correct:
Without the use of ranges and utilizing MAXIFS, this would have been a much more difficult process. There are multiple ways to approach a lookup and it ultimately depends on the situation and what you need to accomplish. MAXIFS and MINIFS are particularly useful when working with dates. But in other situations, you may need to use a different function instead.
If you liked this post on Use MAXIFS and MINIFS to Create a Lookup With Multiple Criteria in Excel, please give this site a like on Facebook and also be sure to check out some of the many templates that we have available for download. You can also follow us on Twitter and YouTube.