To combine variable-size lookup results in Excel for Mac, use REDUCE with VSTACK to build one rectangular array. This walkthrough creates an art-asset list, preserves request order, and shows unmatched groups instead of silently omitting them.

The task is straightforward: each requested asset group can match several source rows, one row, or none. You want all those results in one expanding worksheet range, with asset labels and quantities kept together.

Which Excel for Mac versions support this approach?

Use Excel for Microsoft 365 for Mac for the main formula below. Microsoft explicitly lists that edition on its REDUCE function documentation. Its current page does not list Excel 2024 for Mac, so the presence of other modern array functions alone does not establish that the complete formula is available.

Function Mac editions listed in the relevant Microsoft documentation Role here
BYROW, MAP Microsoft 365 for Mac; Excel 2024 for Mac Explain the original approach and its boundary
REDUCE Microsoft 365 for Mac Carry the growing result between requests
VSTACK, HSTACK Microsoft 365 for Mac; Excel 2024 for Mac Join result blocks and construct a fallback row
FILTER Microsoft 365 for Mac; Excel 2024 for Mac; Excel 2021 for Mac Return every matching asset row

These edition listings come from Microsoft’s BYROW, MAP, VSTACK, HSTACK, and FILTER reference pages.

To inspect your installation, open the Excel menu and choose About Excel. Microsoft’s Mac instructions explain that an Office application’s About dialog shows its version and license type. Check both when deciding which formula to use. See About Office: What version of Office am I using?.

A newer Mac Beta preview changes the nested-array boundary

Microsoft’s September 24, 2026 announcement introduces nested arrays and FLATTEN as preview features for the Beta Channel. It specifies Mac Version 16.114 Build 26092111 or later, with availability subject to rollout. Most nested-array calculations require workbook Compatibility Version 3, selected through Formula > Calculation Options. These conditions matter: having Microsoft 365 alone does not establish that the preview is enabled. See Put multiple values in one cell with lists and arrays in Excel.

The worked solution below builds an ordinary rectangular result without relying on that preview. This also avoids making the workbook’s basic asset-list task depend on nested-array settings.

Why does returning arrays from BYROW or MAP cause trouble?

Microsoft documents BYROW as producing one result per input row. Its reference explicitly states that returning anything other than a single value from the inner LAMBDA produces a #CALC! error. A lookup that returns several records exceeds that documented boundary. See BYROW function.

For example, after entering the sample data in the next section, this is a diagnostic example of the problematic approach under the documented single-value behavior:

=BYROW(E2:E4,LAMBDA(key,FILTER(A2:C7,A2:A7=key,"No match")))

The first request returns three rows and three columns. Asking BYROW to place that block into one result position creates a nested-array problem. Changing the outer function to MAP does not provide a general stacking operation under the conventional calculation behavior: the inner result still contains multiple cells. Microsoft’s MAP documentation describes mapping input values to new values, while its #CALC! guidance documents the nested-array limitation.

Wrapping the failed calculation in VSTACK cannot repair the inner result. The useful change is to append each lookup block while processing the requests, so every intermediate result is already one rectangular array.

Build a complete asset lookup with REDUCE and VSTACK

1. Enter the synthetic source data

Start with a blank worksheet. Paste this tab-separated block into A1. The quantities are illustrative counts of art assets.

Group	Asset	Quantity
Mosaic	Tile Amber	2
Orbit	Ring Silver	3
Mosaic	Tile Blue	4
Prism	Panel Clear	5
Mosaic	Tile Coral	1
Prism	Panel Frost	2

Paste the following request list into E1. Keep the order shown so you can compare the expected result.

Requested group
Mosaic
Orbit
Pebble

This sample deliberately includes three outcomes: Mosaic has three matching records, Orbit has one, and Pebble has none. The Prism records should remain outside the output because that group was not requested.

2. Check one lookup independently

Enter this formula in an unused area, such as L1:

=FILTER($A$2:$C$7,$A$2:$A$7=$E$2,"No match")

It should return the three Mosaic records. FILTER uses the group comparison to select complete rows from the source. Its inclusion array must have a compatible height or width; here both the source and condition cover rows 2:7. See FILTER function.

This small check isolates the lookup from the stacking logic. If these rows are wrong, correct the source labels or references before adding the outer calculation.

3. Enter the complete stacking formula

Leave space in columns G:I, then enter this formula in G1:

=REDUCE(
  $A$1:$C$1,
  $E$2:$E$4,
  LAMBDA(acc,key,
    VSTACK(
      acc,
      FILTER(
        $A$2:$C$7,
        $A$2:$A$7=key,
        HSTACK(key,"No match","")
      )
    )
  )
)

Press Return. Enter the formula once in the top-left output cell; Excel sizes the spill range automatically. Keep it outside an Excel table, because spilled array formulas are not supported inside tables. Microsoft explains both behaviors in Dynamic array formulas and spilled array behavior.

4. Compare the expected result

For the supplied data, the output occupies G1:I6:

Group Asset Quantity
Mosaic Tile Amber 2
Mosaic Tile Blue 4
Mosaic Tile Coral 1
Orbit Ring Silver 3
Pebble No match

The expected dimensions follow directly from the sample: one header row, three Mosaic rows, one Orbit row, and one unmatched-request row. The four matching records contain quantities totaling 10. The blank quantity beside Pebble distinguishes a missing lookup result from an asset quantity of zero.

How the formula assembles the result

REDUCE starts with A1:C1 as its accumulator. For each value in E2:E4, key holds the current requested group and acc holds the result assembled so far. Microsoft documents this initial-value, accumulator, and current-value pattern in REDUCE function.

In this formula, FILTER creates the next matching block and VSTACK appends it beneath acc. Using the actual header as the initial value gives the result three columns immediately and avoids introducing a disposable blank starter row. VSTACK appends arrays vertically in sequence, as described in VSTACK function.

When there are no matches, the third argument of FILTER supplies HSTACK(key,"No match",""). This creates a three-column fallback row: requested group, explanatory label, and blank quantity. Microsoft documents the empty-result argument in FILTER function and horizontal joining in HSTACK function.

Keeping that fallback three columns wide matters. Microsoft documents that VSTACK fills missing columns with #N/A when its input arrays have different widths. A single-cell fallback would therefore leave unwanted padding beside it. See VSTACK function.

Choose the output behavior before extending the worksheet

The formula implements a specific policy: process every request in order, retain all matching source rows, and show one marker row for an unmatched group. Repeating a group in the request list consequently repeats its matching block. Repeating a source record also preserves that record; this formula contains no duplicate-removal step.

Use this decision aid when adapting the example:

Desired result Suitable approach Condition
One count for each group MAP with COUNTIF Each request produces a single value
All asset records in request order The complete REDUCE formula above Requires the documented Microsoft 365 for Mac functions
A few explicitly listed lookup blocks Direct VSTACK of separate FILTER calls Add a formula argument for each request
Nested arrays retained inside cells Microsoft’s nested-array preview Requires the documented Beta availability and workbook settings

For a compact count check, enter this complete formula in K6, leaving three cells below it available:

=MAP($E$2:$E$4,LAMBDA(key,COUNTIF($A$2:$A$7,key)))

The expected counts are 3, 1, and 0. This is an appropriate use of MAP because each request produces one number. COUNTIF counts cells meeting a criterion; the example uses simple synthetic labels. See MAP function and Use the COUNTIF function in Microsoft Excel.

A fixed-request alternative for Excel 2024 for Mac

If your edition provides FILTER, HSTACK, and VSTACK but does not provide REDUCE, use this complete alternative in G1, replacing the previous formula. It produces the same expected output for the three supplied requests:

=VSTACK(
  $A$1:$C$1,
  FILTER($A$2:$C$7,$A$2:$A$7=$E$2,HSTACK($E$2,"No match","")),
  FILTER($A$2:$C$7,$A$2:$A$7=$E$3,HSTACK($E$3,"No match","")),
  FILTER($A$2:$C$7,$A$2:$A$7=$E$4,HSTACK($E$4,"No match",""))
)

Microsoft lists Excel 2024 for Mac on the documentation for all three required functions: FILTER, HSTACK, and VSTACK. The tradeoff is maintenance: another requested group needs another FILTER argument.

Diagnose errors and keep the result complete

For a #SPILL! error, select the formula cell and inspect the intended output area. Move existing entries out of that area and check for merged cells. Microsoft also identifies formulas placed inside tables as a cause; keep the output in the ordinary worksheet grid. See How to correct a #SPILL! error.

For #CALC!, first identify which calculation produced it. A BYROW formula returning a multi-cell block reaches its documented single-value boundary. A standalone FILTER with no matches and no fallback can instead produce an empty-array error. These are separate causes described in Microsoft’s #CALC! guidance.

When adding source records, extend both $A$2:$C$7 and $A$2:$A$7 to the same ending row. When adding requests to the REDUCE version, extend $E$2:$E$4 to the last intended request. Avoid including unused request cells: the formula processes every cell in that range.

For a source list that grows regularly, consider storing the source in an Excel table and replacing the fixed source references with structured references. Microsoft documents that structured references adjust as table rows are added or removed. Keep the spilling result outside the table. See Dynamic array formulas and spilled array behavior.

Before using the assembled asset list, compare its group blocks with the request list and check the unmatched markers. In this example, the useful completion check is concrete: three Mosaic records, one Orbit record, one visible Pebble marker, and no Prism records. That confirms the intended selection and ordering for the supplied data.

Sources