How to find the cheapest option that meets a minimum requirement in Excel
Formula (Excel and Google Sheets)
=XLOOKUP(1,(B2:B8>=F1)*(C2:C8=MINIFS(C2:C8,B2:B8,">="&F1)),A2:A8,"None")Find the lowest price among the options that qualify, then look up that option: =XLOOKUP(1,(B2:B8>=F1)*(C2:C8=MINIFS(C2:C8,B2:B8,">="&F1)),A2:A8,"None").
- Result on the example
- Family in
F2 - Works in
- Excel 2021, 2024 and Microsoft 365
- Google Sheets
- Same formula
See it work
The formula is already in F2. Change any input and the result updates, or edit the formula to experiment.
How it works
MINIFS(C2:C8,B2:B8,">="&F1) is the lowest price among plans with at least 15 GB: $34. The lookup then needs both tests, enough data and that price, because a plan that doesn't qualify could cost the same. The answer is Family, which beats the bigger Plus plan on price.
=XLOOKUP(1,(B2:B8>=F1)*(C2:C8=MINIFS(C2:C8,B2:B8,">="&F1)),A2:A8,"None")Result"Family"
B2:B87 rows × 1 col: 5, 10, 20, 30, …A range: 7 cells in one column.
F115The value in F1.
B2:B8>=F1{7 rows × 1 col} FALSE, FALSE, TRUE, TRUE, …The >= operator tests at least.
C2:C87 rows × 1 col: $15, $25, $35, $38, …A range: 7 cells in one column.
">="&F1">=15"The & operator joins as text.
MINIFS(C2:C8,B2:B8,">="&F1)34MINIFS: Smallest value that meets every condition.- min_range
C2:C87 rows × 1 col: $15, $25, $35, $38, … - criteria_range1
B2:B87 rows × 1 col: 5, 10, 20, 30, … - criteria1
">="&F1">=15"
- min_range
C2:C8=MINIFS(C2:C8,B2:B8,">="&F1){7 rows × 1 col} FALSE, FALSE, FALSE, FALSE, …The = operator tests whether the two sides are equal.
B2:B8>=F1)*(C2:C8=MINIFS(C2:C8,B2:B8,">="&F1){7 rows × 1 col} 0, 0, 0, 0, …The * operator multiplies.
A2:A87 rows × 1 col: "Starter", "Basic", "Plus", "Value 30", …A range: 7 cells in one column.
XLOOKUP(1,(B2:B8>=F1)*(C2:C8=MINIFS(C2:C8,B2:B8,">="&F1)),A2:A8,"None")"Family"XLOOKUP: Searches one range and returns the matching item from another.- lookup_value
1 - lookup_array
B2:B8>=F1)*(C2:C8=MINIFS(C2:C8,B2:B8,">="&F1){7 rows × 1 col} 0, 0, 0, 0, … - return_array
A2:A87 rows × 1 col: "Starter", "Basic", "Plus", "Value 30", … - if_not_found
"None"
- lookup_value
In Google Sheets
The same formula works in Google Sheets: =XLOOKUP(1,(B2:B8>=F1)*(C2:C8=MINIFS(C2:C8,B2:B8,">="&F1)),A2:A8,"None"). We ran it in Google Sheets mode on the same data and it returns Family.
Other ways to do it
Each one was run on the same example sheet.
| Formula | Result | When to use it |
|---|---|---|
| =INDEX(SORT(FILTER(A2:C8,B2:B8>=F1),3,1),1,1) | Family | Keep the qualifying plans, sort them by price and take the first. Excel 2021, Microsoft 365 and Google Sheets. |
Common mistakes
- Looking up the price alone can return a cheaper-looking plan that doesn't meet the requirement but happens to cost the same.
- Plans tied on price return the first one listed. Add a second sort key if that matters.
Try it yourself
Same data, empty answer cell. Your formula is graded instantly, then re-checked on changed data, so a typed-in number won't pass.
Find the cheapest option that meets a minimum requirement
F2, return the cheapest plan that includes at least the data in F1.Questions
How do I add a second requirement?
Add it to both MINIFS and the lookup, for example a minutes column: MINIFS(C2:C8,B2:B8,">="&F1,D2:D8,">="&F3).
How do I return the price as well?
Use the MINIFS part on its own in the next cell.
Checked by running on 2026-10-30: every formula and result on this page was calculated by the Spreadsheet Practice formula engine, in both Excel and Google Sheets modes.