Use FILTER to isolate an exact art-asset category, then XLOOKUP to return the tier at the largest quantity threshold that does not exceed your count. This walkthrough builds a complete Excel for Mac example with synthetic labels and boundary tests.

Which Excel for Mac versions support this formula?

The formula below uses LET, FILTER, and XLOOKUP. Microsoft’s documentation lists all three for Excel for Microsoft 365 for Mac, Excel 2024 for Mac, and Excel 2021 for Mac. Microsoft also identifies these functions in its overview of what’s new in Excel 2021 for Mac.

Excel 2016 and Excel 2019 cannot evaluate XLOOKUP; Microsoft explicitly documents that limitation. For this exact formula, use one of the Mac editions listed above. Opening a workbook containing the function in an older edition does not add function support. See Microsoft’s XLOOKUP documentation.

If you are unsure which edition is installed, check the application’s About dialog for its version and license type. Microsoft explains how to identify these details in About Office: What version of Office am I using?.

Set up category-specific minimum quantities

Suppose you maintain counts of reusable graphic assets and want a descriptive inventory tier for each category. The task has two rules: the category must match, and the quantity must meet a tier’s minimum. All labels and quantities below are illustrative.

On a blank worksheet, enter this table in A1:C7. The header occupies row 1; the six data rows occupy rows 2–7. Enter the minimum quantities as numbers and fill every category and tier cell.

Category Minimum quantity Tier label
Texture Tile 5 Compact
Texture Tile 20 Expanded
Texture Tile 50 Extensive
Shape Stamp 5 Compact
Shape Stamp 30 Expanded
Shape Stamp 80 Extensive

Next, enter Category to check in E1, Quantity in F1, and Inventory tier in G1. Put Texture Tile in E2 and the number 34 in F2. Leave column D empty to separate the reference data from the inputs.

Treat the minimum quantities as inclusive lower boundaries. For Texture Tile, Compact starts at 5, Expanded starts at 20, and Extensive starts at 50. Therefore, a count of 34 belongs to Expanded. For Shape Stamp, the same labels begin at different quantities: a count of 20 still belongs to Compact.

Before writing the formula, decide whether your own boundaries represent minimums or maximums. This example uses minimums throughout. It also leaves quantities below 5 outside every tier and lets the final tier continue above its starting quantity. Those are explicit design choices for this sample, so adjust the reference data if your intended rules differ.

Enter the complete category-and-quantity formula

Select G2, paste the following complete formula into the formula bar, and press Return. It uses the exact worksheet layout above; no named ranges or helper columns are required.

=LET(
  category,$E2,
  quantity,$F2,
  categories,$A$2:$A$7,
  minimums,$B$2:$B$7,
  labels,$C$2:$C$7,
  keep,categories=category,
  IF(
    OR(category="",NOT(ISNUMBER(quantity))),
    "Enter category and numeric quantity",
    IF(
      quantity<0,
      "Use zero or more",
      IF(
        SUM(--keep)=0,
        "Category not found",
        XLOOKUP(
          quantity,
          FILTER(minimums,keep),
          FILTER(labels,keep),
          "Below first tier",
          -1
        )
      )
    )
  )
)

For Texture Tile and 34, the expected result is Expanded. The formula returns a single label even though its intermediate calculations use arrays. Modern dynamic array formulas use ordinary entry; Microsoft documents confirming them with Enter in Dynamic array formulas and spilled array behavior.

How the category filter keeps the arrays aligned

The expression categories=category creates the inclusion array named keep. For this sample input, it selects the three Texture Tile rows. Both FILTER calls use that same array, producing minimums of 5, 20, and 50 alongside the corresponding labels Compact, Expanded, and Extensive.

Microsoft documents FILTER as selecting data with a Boolean inclusion array whose dimensions match the source range. Using the same inclusion array for both columns preserves their correspondence. See the FILTER function.

The outer LET assigns readable names to the source ranges, inputs, and inclusion array. These names exist only inside this formula; you do not need to create them separately in the workbook. Microsoft describes that behavior in its LET function documentation.

How the quantity selects the tier

The fifth XLOOKUP argument, -1, requests an exact quantity match or the next smaller value. Within the filtered Texture Tile rows, 34 selects the minimum of 20 and returns Expanded. An exact quantity of 50 selects Extensive. Microsoft defines these modes in the XLOOKUP function documentation.

The formula also distinguishes an unknown category from a count below the first tier. SUM(--keep) counts the matching category rows. When there are none, the formula returns Category not found before attempting the filtered lookup. When a category exists but its minimums all exceed the count, the lookup returns Below first tier.

Test boundaries and category isolation

Replace E2 and F2 with the following pairs, one at a time. These checks are more useful than testing only a middle value: they exercise the first threshold, exact boundaries, category differences, and missing categories.

Category in E2 Quantity in F2 Expected result
Texture Tile 0 Below first tier
Texture Tile 5 Compact
Texture Tile 19 Compact
Texture Tile 20 Expanded
Texture Tile 34 Expanded
Texture Tile 50 Extensive
Shape Stamp 20 Compact
Shape Stamp 34 Expanded
Shape Stamp 100 Extensive
Color Swatch 34 Category not found

The two tests using a count of 20 are particularly revealing. Texture Tile must return Expanded, while Shape Stamp must return Compact. If both return Expanded, inspect whether the quantity lookup is using the filtered category rows.

These ten expected outcomes were checked with a small Python calculation implementing the stated minimum-threshold rule. The Excel formula itself was inspected against Microsoft’s documented syntax; it was not executed in Excel.

Also check the input messages: a blank category or a quantity entered as text should return Enter category and numeric quantity; a negative numeric quantity should return Use zero or more. Use whole-number inputs for asset counts. The formula checks that quantities are numeric and nonnegative, but does not enforce whole numbers.

Diagnose unexpected results and extend the worksheet

Keep these checks in order so that a formatting problem does not become a change to the tier rules.

  1. Check the category text. Compare the input with the reference labels, including spaces. Microsoft identifies unexpected characters and hidden spaces as common lookup problems in its lookup troubleshooting guidance.
  2. Check numeric cells. Quantity inputs and minimums should contain numbers, rather than numeric-looking text. The same Microsoft guidance identifies inconsistent data types as a lookup problem.
  3. Check range alignment. The category, minimum, and label ranges must cover the same data rows. In this example, every range begins at row 2 and ends at row 7.
  4. Check duplicate rules. Give each category and minimum-quantity pair one tier label. If two rows assign different labels to the same boundary, resolve that ambiguity in the reference data.
  5. Check the boundary convention. A result that appears one tier too high or low may indicate that your reference values are maximums, while this formula expects minimums.

For a visual inspection, enter the following diagnostic formula in an unused cell such as I2. With Texture Tile in E2, it should display the three complete Texture Tile rows. This is a separate diagnostic formula, not a replacement for G2.

=FILTER($A$2:$C$7,$A$2:$A$7=$E2,"Category not found")

Leave room for that diagnostic result to expand across three columns and down three rows. A standalone array result spills into neighboring cells, and occupied cells can cause #SPILL!. Microsoft explains that behavior in Dynamic array formulas and spilled array behavior.

When you add more reference rows, extend all three source ranges in the main formula together. For example, if the final data row becomes 10, change the definitions to categories,$A$2:$A$10, minimums,$B$2:$B$10, and labels,$C$2:$C$10. Then repeat the boundary tests for the added category.

To process several input rows, enter more category-and-quantity pairs below E2:F2 and copy G2 down. The source ranges remain fixed because their references contain dollar signs; the input row numbers advance. Keep category-specific boundaries explicit, and use the paired category tests to confirm that each returned label follows the intended inventory rule.

Sources