To find the maximum value in one column based on a criteria in another column, use an array formula like =MAX(IF(A2:A20="North",B2:B20)) (confirm with Ctrl+Shift+Enter in older Excel) or the dynamic =MAXIFS(B2:B20,A2:A20,"North") in Excel 2019 and later. For returning the entire row of that max value, combine INDEX and MATCH with MAXIFS or use FILTER in Microsoft 365.
Why VLOOKUP Alone Can’t Do This
VLOOKUP is designed to find a specific lookup value in the first column of a table and return a value from a column to the right. It cannot compute a maximum or apply a criteria. The typical workaround is to use an array formula or a combination of functions. For example, if you have sales data with regions in column A and amounts in column B, you might want the highest sale for the “North” region. VLOOKUP would require you to know the exact lookup value (e.g., the amount) beforehand, which defeats the purpose.
The Core Formula: MAXIFS
Starting with Excel 2019 and Microsoft 365, the MAXIFS function is the simplest and most efficient way to get the maximum value based on one or more criteria. Its syntax is MAXIFS(max_range, criteria_range1, criteria1, [criteria_range2, criteria2], ...).
Example: Assume data in A2:A20 contains region names (“North”, “South”, etc.) and B2:B20 contains sales amounts. To get the max sales for “North”:
- In cell D2, enter:
=MAXIFS(B2:B20,A2:A20,"North") - The result will be the highest value in B2:B20 where the corresponding cell in A2:A20 equals “North”.
For multiple criteria, add more ranges and criteria, e.g., =MAXIFS(B2:B20,A2:A20,"North",C2:C20,"Q1") if column C has quarter labels.
Classic Array Formula (Pre-2019)
If you use Excel 2016 or earlier, MAXIFS is not available. Instead, use an array formula with MAX and IF:
- Select a cell, e.g., D2.
- Type:
=MAX(IF(A2:A20="North",B2:B20)) - Press Ctrl+Shift+Enter (on Mac: Cmd+Shift+Enter) to enter it as an array formula. Excel will enclose it in curly braces in the formula bar, like
{=MAX(IF(...))}.
This works by evaluating the IF for each cell in A2:A20, returning the corresponding B value if true, and FALSE otherwise. MAX ignores FALSE values. Note that array formulas can be slower on large datasets, but they are reliable.
Returning the Entire Row of the Max Value
Often you need not just the max number, but the full record (e.g., the salesperson, date, and amount). To do that, combine INDEX and MATCH with MAXIFS (or array formula).
Example layout: A2:A20 = Salesperson, B2:B20 = Region, C2:C20 = Amount. You want the row for the highest amount in “North”.
- First, get the max amount:
=MAXIFS(C2:C20,B2:B20,"North")in cell E2. - Then, find its row number with MATCH:
=MATCH(1,(B2:B20="North")*(C2:C20=E2),0)entered as an array formula (Ctrl+Shift+Enter). This returns the position within A2:C20. - Finally, use INDEX to pull the salesperson:
=INDEX(A2:A20,MATCH(1,(B2:B20="North")*(C2:C20=E2),0))(also array-entered).
If there are duplicate max values, this returns the first occurrence. To get all rows, use the FILTER function in Microsoft 365: =FILTER(A2:C20,(B2:B20="North")*(C2:C20=MAXIFS(C2:C20,B2:B20,"North")),"No match").
Using SUMPRODUCT for a Single Max
Another pre-2019 alternative is SUMPRODUCT with MAX, but it requires entering as a normal formula because SUMPRODUCT handles arrays natively. For example, =SUMPRODUCT(MAX((A2:A20="North")*B2:B20)) will return the max value for “North”. However, this only works if all values are positive; if negative values exist, it fails because it multiplies by 0 for non-matching rows. Use the array formula method for negative values.
Common Mistakes and How to Avoid Them
- Forgetting to press Ctrl+Shift+Enter for array formulas – if you just press Enter, you may get a #VALUE! error or an incorrect result.
- Mismatched range sizes – all ranges must have the same number of rows and columns, otherwise you get #VALUE!.
- Using whole columns (e.g., A:A) with array formulas can slow down Excel significantly; limit to your actual data range.
- Text vs. number criteria – if your criteria cell contains text, use quotes; if it contains a number, no quotes.
- Case sensitivity – MAXIFS and IF are not case-sensitive. If you need case-sensitive matching, use
EXACTin an array formula:=MAX(IF(EXACT(A2:A20,"north"),B2:B20)).
Comparing MAXIFS, Array Formulas, and PivotTables
For a one-off calculation, MAXIFS is the easiest. For older Excel versions, array formulas are the standard. If you need to see max values for many categories at once, a PivotTable is often more practical: drag the region field to Rows, the amount field to Values, then set the value field to Max (right-click the field > Summarize Values By > Max). This requires no formulas and updates automatically when data changes. The downside is that it doesn’t embed in a cell as a formula.
Frequently Asked Questions
Can I use VLOOKUP with MAX in the same formula? Not directly. VLOOKUP needs a lookup value, not a calculation. You can use a helper column that contains the max value per criteria, but that’s redundant with MAXIFS.
Why does my array formula return #VALUE!? Usually because you didn’t press Ctrl+Shift+Enter, or the ranges are different sizes. Check that all ranges have the same number of rows.
How do I find the max value with multiple criteria? Use MAXIFS with multiple range/criteria pairs, e.g., =MAXIFS(C2:C20,A2:A20,"North",B2:B20,"Q1"). For array formulas, add another IF: =MAX(IF(A2:A20="North",IF(B2:B20="Q1",C2:C20))).
Does MAXIFS work in Excel for Mac? Yes, MAXIFS is available in Excel 2019 and later for Mac, and in Microsoft 365 for Mac. The array formula method also works with Cmd+Shift+Enter.
What if I have duplicate max values and want the last one? Use AGGREGATE with LARGE, or use an array formula with MAX and ROW to find the last row, but it’s complex. Simpler: use FILTER in Microsoft 365 to get all matches, then take the last row.
Can I use MAXIFS with a cell reference for the criteria? Yes, instead of “North”, use a cell reference like =MAXIFS(B2:B20,A2:A20,D2) where D2 contains the region.
Related
- Excel Spreadsheet Timesheet Template: How to Make One
- Excel Spreadsheet to Track Job Applications? Yes, Here’s How
- Excel Spreadsheet for Inventory Tracking: How to Build One
Try it: the Formula Explainer: paste any formula and see each function and reference in plain English.
More in Formulas & functions
- Excel Round to Nearest X: The Complete Formula GuideTo round a number to the nearest specified multiple in Excel, use the MROUND function: =MROUND(number, multiple). For…
- COUNTIF Negative Numbers in Excel: Formula and TipsIf your values are in A2:A20, count the negative numbers with =COUNTIF(A2:A20,"<0"). COUNTIF returns the number of cells…
- Excel Formula to Calculate Hours WorkedFor a shift that starts in B2, ends in C2, and has an unpaid break entered as a…
- Copy and Transpose in Excel: Steps, Formulas, and TipsTo copy and transpose data in Excel for Microsoft 365 on Windows, select and copy the cells, choose…
- Excel Link to Named Range: Formulas and MethodsTo create a clickable link to a named range in Excel for Microsoft 365 on Windows, use the…
- Excel Filter Shortcut: Apply, Use, and Clear FiltersIn Excel for Microsoft 365 on Windows, press Ctrl+Shift+L to turn column filters on or off for the…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.