When You Need Maximum or Minimum Values with VLOOKUP: Complete Guide to Finding Them Accurately

VLOOKUP max min problems fall into two categories: (1) finding the maximum/minimum value itself, and (2) finding the label (name or code) for the row with that value. The solution also differs depending on whether you need an exact match or an approximate match, and whether the result is criteria-based (by category). This article provides the safest and shortest formulas as quick fixes for each situation, then explains why they work.

Quick Fix: Five Ready-to-Use Formulas by Scenario

1. ① You Need Only the Value — Overall Maximum/Minimum

   =MAX(데이터범위)    // maximum
   =MIN(데이터범위)    // minimum

   The simplest option. You do not need VLOOKUP.

2. ② You Need a Label — Name of the Row with the Maximum Value (Exact Match)

   =LET(
     vals, B2:B101, labels, A2:A101,
     INDEX(labels, XMATCH(MAX(vals), vals, 0))
   )

   How it works: MAX finds the maximum value, XMATCH finds its exact-match position, and INDEX returns the label. (Use MATCH if XMATCH is unavailable.)

3. ③ You Need a Label — Name of the Minimum Value (First Match for Duplicates)

   =INDEX(A2:A101, XMATCH(MIN(B2:B101), B2:B101, 0))

   If multiple entries have the same value, this returns the first item. For a Top N list that includes ties, see the advanced examples below.

4. ④ Criteria-Based Maximum/Minimum (by Category) — Value Only

   =MAXIFS(값범위, 조건범위, 조건)   // Excel 2019+/Microsoft 365
   =MINIFS(값범위, 조건범위, 조건)

   For example: =MAXIFS(매출[금액], 매출[카테고리], "Shoes"). In earlier versions, use the MAX(IF(...)) array formula instead.

5. ⑤ Label for a Criteria-Based Maximum/Minimum (Name or Code) — XLOOKUP Combination

   =LET(
     flt, FILTER(A2:C101, C2:C101="Shoes"),   // [name, value, category]
     names, TAKE(flt,,1), vals, TAKE(flt,,2),
     XLOOKUP(MAX(vals), vals, names, "Not found", 0)
   )

   If dynamic arrays are unavailable, you can achieve the same result with an INDEX/MATCH combination.

How to Find an Approximate Maximum/Minimum with VLOOKUP (Sorting Required)

Sorting is essential when using VLOOKUP in approximate match mode (TRUE as the final argument, or omitted).

• Ascending sort (small to large): =VLOOKUP(찾을값, 표, 열번호, TRUE) finds the largest value less than or equal to the lookup value. Useful for finding a lower boundary.
• Descending sort (large to small): Not recommended. VLOOKUP is designed for ascending order, and descending order can produce unpredictable results.

Recommended pattern: For range mappings such as “score to grade,” sort boundary values in ascending order, then return the grade using approximate match mode.

Boundary table (ascending): 0-F, 60-D, 70-C, 80-B, 90-A
=VLOOKUP(점수, 경계표, 2, TRUE)

Why It Is Difficult to Directly Return the “Label for the Maximum/Minimum” with VLOOKUP

VLOOKUP looks for a key in the leftmost column and can return only values to the right. To find the name of the row with the maximum value, the value range must be the key, but values are usually on the right, making a direct lookup difficult. You can work around this in two ways.

1. INDEX/XMATCH + MAX/MIN (recommended): No direction limitation, and it handles multiple criteria and duplicates more easily.
2. Create a virtual two-column array with CHOOSE + VLOOKUP (possible): Move the value column to the left to force the lookup.

=VLOOKUP(MAX(B2:B101), CHOOSE({1,2}, B2:B101, A2:A101), 2, FALSE)

Explanation: CHOOSE({1,2}, value, label) creates a virtual [value | label] table, then finds the maximum value with an exact match and returns its label.

Practical Examples: Reproducible Sample

Data table ( A1:C11 ) — A: Name, B: Sales, C: Category

Example 1 — Name with the Highest Overall Sales

=INDEX(A2:A7, XMATCH(MAX(B2:B7), B2:B7, 0))   

Example 2 — Highest Sales in the Shoes Category (Value Only)

=MAXIFS(B2:B7, C2:C7, "Shoes")                 

Example 3 — Name with the Highest Sales in the Shoes Category

=LET(
  flt, FILTER(A2:C7, C2:C7="Shoes"),
  names, TAKE(flt,,1), vals, TAKE(flt,,2),
  XLOOKUP(MAX(vals), vals, names, "Not found", 0)
)                                            

Example 4 — Names with the Bottom 2 Sales Values (Including Ties)

=LET(
  k, 2,
  idx, XMATCH(SMALL(B2:B7, SEQUENCE(k)), B2:B7, 0),
  INDEX(A2:A7, idx)
)

With dynamic arrays, you can return the top or bottom N names at once to create a report box.

Checklist (Essential Habits)

• Distinguish exact and approximate modes: Use approximate match (TRUE) for range mappings and exact match (FALSE) to find a specific value.
• Sorting rule: Approximate match (TRUE) requires ascending order. Incorrect sorting produces incorrect results.
• Handle duplicates: XMATCH returns only the first match for ties. For Top N, use LARGE/SMALL + INDEX/XMATCH.
• Direction limitation: VLOOKUP returns values only to the right. If you need to look left, use INDEX/XMATCH or work around it with CHOOSE.
• Error handling: Use IFERROR( … , "Not found") to maintain report quality.

Troubleshooting

Alternatives and Advanced Uses: Modern Function Combinations

• XLOOKUP: Handles exact/approximate matches, forward/backward lookups, and a default value when not found in one function. For a maximum/minimum label, XLOOKUP(MAX(vals), vals, names, , 0) is intuitive.
• MAXIFS/MINIFS: Calculate criteria-based maximums/minimums directly without a helper column.
• TAKE/FILTER/LET: Create report blocks (Top/Bottom N) with dynamic arrays.

Related Articles

• VLOOKUP Exact vs. Approximate Match
• XLOOKUP Multiple-Criteria Patterns
• Understanding Array Calculations with SUMPRODUCT

External Sources (Authoritative Documentation)

• Microsoft Support — VLOOKUP function
• Microsoft Support — XLOOKUP function
• Microsoft Support — MAXIFS/MINIFS functions

Conclusion — The primary situation where you would use VLOOKUP directly for maximum/minimum values is ascending-order approximate match mode (range mapping). For returning a label or finding criteria-based maximums/minimums, INDEX/XMATCH + MAX/MIN or MAXIFS/MINIFS · XLOOKUP is more accurate and reliable.