To make an Excel inventory lookup choose the most specific matching rule, first identify eligible rows, then count their specified fields. Return the quantity from the unique highest-scoring row, and flag ties. This walkthrough builds that result for synthetic art assets in Excel for Mac.

Which Excel for Mac versions support this approach?

The formula below uses LET, XLOOKUP and dynamic array calculations. Microsoft documents these capabilities for Excel for Microsoft 365 for Mac, Excel 2024 for Mac and Excel 2021 for Mac. Its Excel 2021 for Mac feature guide explicitly includes LET, XLOOKUP and dynamic arrays. What's new in Excel 2021 for Mac, LET function.

Excel 2016 and Excel 2019 do not support XLOOKUP, according to Microsoft's explicit compatibility note. Use one of the supported Mac editions for this exact formula; changing how you enter the formula does not supply a missing function. The optional inspection formula later in this article uses FILTER, also documented for the three supported Mac editions above. XLOOKUP function, FILTER function.

What should “most specific” mean in your inventory?

For this example, you are looking up a quantity assigned to an art asset using three attributes: asset label, finish and size. A blank rule field means that the rule accepts any value for that attribute. A populated field must equal the corresponding input. Among eligible rules, the rule with the most populated fields wins.

This is an explicit workbook policy derived for this example. Excel does not infer that policy from your descriptions. Here, every attribute has equal weight: a rule specifying asset and finish has the same specificity as one specifying asset and size. If both match and share the highest score, the formula reports a tie.

A plain XLOOKUP cannot decide this precedence on its own. Microsoft documents that it returns the first matching item by default. Reversing the search changes which end it starts from; it does not rank rules by specificity. The formula therefore calculates the winning row before asking XLOOKUP for its quantity. XLOOKUP function.

Set up the synthetic art-asset rules

On a blank worksheet, enter these headers and values in A1:D6. Empty cells in the table below must remain empty in Excel. All labels and quantities are illustrative.

Worksheet row A: Asset B: Finish C: Size D: Quantity
2 30
3 Tile 18
4 Tile Matte 12
5 Tile Matte Small 7
6 Disc Large 10

The rule in row 2 is an intentional default: it specifies no attributes and has specificity zero. Row 3 specifies one attribute, row 4 specifies two, and row 5 specifies three. These quantities are alternative assigned quantities, so the lookup returns one value. It does not add the default and exception quantities together.

Enter the headers Asset, Finish, Size and Result in H1:K1. Then enter Tile in H2, Matte in I2 and Small in J2. Keep all three input attributes populated. In this design, blanks act as unrestricted conditions only in the rule table; incomplete input receives a message.

Use numeric quantities, including numeric zero when appropriate. Keep the ranges restricted to actual rule rows. Extending them through unused blank rows would introduce extra all-blank rules under this matching policy.

Enter the formula that selects the winning rule

Select K2, enter this complete formula, and press Return. It uses the rule table and input cells defined above. Dynamic array calculations are evaluated within the formula; the final result occupies one cell. Microsoft's documentation describes entering dynamic array formulas normally, without legacy array-entry keystrokes. Dynamic array formulas and spilled array behavior.

=IF(OR(H2="",I2="",J2=""),"Complete the asset details",
  LET(
    asset,$A$2:$A$6,
    finish,$B$2:$B$6,
    size,$C$2:$C$6,
    qty,$D$2:$D$6,
    eligible,
      (((asset="")+(asset=H2))>0)*
      (((finish="")+(finish=I2))>0)*
      (((size="")+(size=J2))>0),
    specificity,--(asset<>"")+--(finish<>"")+--(size<>""),
    best,MAX(IF(eligible,specificity,-1)),
    winner,eligible*(specificity=best),
    IF(SUM(eligible)=0,"No matching rule",
      IF(SUM(winner)>1,"Tie: review rules",
        IF(XLOOKUP(1,winner,ISNUMBER(qty),FALSE,0),
          XLOOKUP(1,winner,qty,"No matching rule",0),
          "Check rule quantity"
        )
      )
    )
  )
)

With the sample inputs, K2 should return 7. Four rules are eligible, but only the rule in row 5 specifies all three attributes.

How eligibility and specificity work

LET assigns local names to the ranges and intermediate results, making the selection process easier to inspect. Microsoft documents that these names exist within the formula's scope. LET function.

For each attribute, the eligibility expression asks whether the rule cell is blank or equals the input. Addition combines those two possibilities, and the comparison converts the result into a logical test. Multiplying the three attribute tests requires every condition to pass. Microsoft uses the same multiplication pattern for combining array criteria with AND logic in its FILTER examples. FILTER function.

specificity counts populated rule fields. The double unary operators convert each nonblank test into a number, producing scores from zero through three. IF assigns ineligible rows a score of negative one for the ranking calculation; MAX then finds the highest eligible score. Negative one keeps an ineligible row below even the unrestricted default's score of zero. IF function, MAX function.

winner marks only eligible rows with that highest score. The formula checks for no match and multiple winners before returning a quantity. Its final numeric check deliberately rejects a blank quantity or a number stored as text. Microsoft documents that ISNUMBER does not convert text numbers into numeric values. A numeric zero remains a valid result. IS functions.

Check the results before extending the worksheet

Change H2:J2 to each combination below. These are expected results derived from the sample rules, not measured inventory observations.

Asset Finish Size Expected result Winning condition
Tile Matte Small 7 Three specified fields
Tile Matte Large 12 Asset and finish
Tile Gloss Small 18 Asset only
Disc Gloss Large 10 Asset and size
Arc Matte Small 30 Unrestricted default

Next, temporarily change row 6 to asset Tile, finish Matte, size Small and quantity 9. With the original inputs restored, the result should become Tie: review rules: two rules now specify all three matching attributes. Even identical quantities would still produce a tie, because the rule selection is ambiguous. Restore the original row 6 afterward.

To test the no-match branch, temporarily change the formula's four rule ranges to start at row 3, excluding the default. The input Arc, Matte, Small should then return No matching rule. Restore the ranges to start at row 2 when finished. To test the quantity check, clear D5 while using the original inputs; expect Check rule quantity, then restore 7.

Inspect overlapping rules and resolve ties

To see all eligible rules for the current input, enter this separate formula in M2. Leave M2:P6 empty beforehand. The output shows matching rule rows in their original order; it does not rank them.

=FILTER(
  $A$2:$D$6,
  ((($A$2:$A$6="")+($A$2:$A$6=H2))>0)*
  ((($B$2:$B$6="")+($B$2:$B$6=I2))>0)*
  ((($C$2:$C$6="")+($C$2:$C$6=J2))>0),
  "No matching rule"
)

FILTER returns the rows selected by its Boolean inclusion array, and its final argument supplies a message when none qualify. Blank source cells can display as zeros in this inspection output; continue treating the blank cells in the original rule table as unrestricted conditions. FILTER function.

The inspection formula spills into neighboring cells. Put it outside an Excel table and keep the output area clear. Microsoft documents that spilled array formulas are unsupported inside tables and that occupied output cells can cause #SPILL!. Dynamic array formulas and spilled array behavior.

Resolve a tie by correcting a duplicate, narrowing an overlapping rule, or defining an additional priority policy. For example, asset-plus-finish and asset-plus-size rules both score two here. The workbook cannot decide which attribute matters more unless you encode that preference. Moving rows should not determine the answer under the policy used in this article.

Extend the lookup without changing its meaning

For another asset, enter its three attributes in H3:J3 and copy K2 into K3. The input references move with the formula, while the dollar signs keep the rule ranges fixed. When adding rules beyond row 6, extend all four ranges in the main formula and both dimensions of the inspection formula consistently.

Keep each attribute in its own column and use consistent labels without incidental spaces. This formula compares whole field values; it does not search for fragments inside a combined asset description. Its quantity check establishes that the chosen value is numeric, so apply any additional inventory requirements—such as nonnegative whole quantities—to the source data.

The resulting lookup has a clear decision sequence: require complete input, identify eligible rules, find the highest specificity, reject ties, and return the selected numeric quantity. That sequence makes general defaults and detailed exceptions usable together while keeping unresolved choices visible.

Sources