Use an exact-match lookup to add asset metadata to every inventory row, including repeated asset IDs. In Excel for Mac, you can build one spilled result with HSTACK and XLOOKUP, or use separate lookup columns in Excel 2021.

This walkthrough is for a Mac desktop user who has many art-asset inventory entries and one metadata row per asset. The task is to attach each asset’s title, format and palette while preserving the inventory’s original order, quantities and repeated entries. All sample data below is synthetic.

Which Excel for Mac version supports this approach?

Check your installation through Excel → About Microsoft Excel. Microsoft’s Mac instructions explain that the application’s About dialog shows its version number and license type. Check both when choosing a formula. About Office: What version of Office am I using?

Mac edition Approach in this article
Excel for Microsoft 365 for Mac One combined result using HSTACK and XLOOKUP
Excel 2024 for Mac One combined result using HSTACK and XLOOKUP
Excel 2021 for Mac Three separate XLOOKUP spill columns
Excel 2019 or 2016 These formulas require a newer edition

Microsoft lists Microsoft 365 for Mac and Excel 2024 for Mac on the HSTACK documentation. Its Excel 2021 for Mac feature documentation confirms XLOOKUP and dynamic arrays. The XLOOKUP documentation explicitly excludes Excel 2016 and 2019.

If you have Excel 2021, use the separate-column method below. It reaches the same enrichment outcome without requiring HSTACK.

Set up inventory and metadata with a shared asset ID

Create two worksheets in the same workbook named Inventory and Metadata. Enter the following inventory data in Inventory!A1:B6. Keep these cells as an ordinary worksheet range for this example.

AssetID Quantity
ARC-01 3
TILE-02 5
ARC-01 2
GRID-03 4
TILE-02 1

Enter the metadata in Metadata!A1:D4:

AssetID Title Format Palette
ARC-01 Arc Study Vector Teal
TILE-02 Tile Study Raster Amber
GRID-03 Grid Study Vector Slate

The repeated inventory IDs are intentional: two entries can describe separate quantities of the same asset. The metadata sheet supplies the shared description once. Your output should therefore contain five data rows, even though the metadata contains only three assets.

Use an asset ID that consistently identifies the same asset in both sheets. For this example, every ID and metadata field is populated. In your own workbook, decide how to represent an unknown field explicitly—for example, with the text Unknown—so it can be distinguished from a missing asset match.

Check that each inventory ID has exactly one metadata match

On Inventory, enter MetadataMatches in C1. Enter this formula in C2, then copy it through C6:

=COUNTIF(Metadata!$A$2:$A$4,A2)

Interpret the result before building the output:

  • 0: the asset ID is missing from the metadata sheet.
  • 1: the asset has the single metadata row this workflow expects.
  • Any number greater than 1: the metadata contains duplicate IDs that need resolving.

COUNTIF counts cells meeting a criterion. For the supplied data, every check returns 1. Use simple IDs such as those shown here: COUNTIF treats asterisks and question marks as wildcard characters, so IDs containing those characters need additional handling. Use the COUNTIF function in Microsoft Excel

Resolve duplicate metadata IDs before continuing. XLOOKUP returns the first match by default, so a populated result alone cannot establish that the metadata key is unique. XLOOKUP function

Build one enriched array in Microsoft 365 or Excel 2024

Create a worksheet named Enriched. Enter these five headings in A1:E1: AssetID, Quantity, Title, Format, Palette. Leave A2:E6 empty.

Select Enriched!A2, enter the complete formula below and press Return:

=HSTACK(
  Inventory!A2:B6,
  XLOOKUP(Inventory!A2:A6,Metadata!A2:A4,Metadata!B2:B4,"MISSING METADATA",0),
  XLOOKUP(Inventory!A2:A6,Metadata!A2:A4,Metadata!C2:C4,"MISSING METADATA",0),
  XLOOKUP(Inventory!A2:A6,Metadata!A2:A4,Metadata!D2:D4,"MISSING METADATA",0)
)

This formula combines the two original inventory columns with three lookup-result columns. Each lookup uses the same five inventory IDs, keeping the added fields aligned with the original entries. HSTACK appends the arrays horizontally in the order supplied. HSTACK function

The final 0 in each lookup requests an exact match. The text MISSING METADATA is the result for an unmatched ID. Keeping that visible helps you locate incomplete matches rather than leaving an unexplained empty cell. XLOOKUP function

The expected output occupies Enriched!A2:E6:

AssetID Quantity Title Format Palette
ARC-01 3 Arc Study Vector Teal
TILE-02 5 Tile Study Raster Amber
ARC-01 2 Arc Study Vector Teal
GRID-03 4 Grid Study Vector Slate
TILE-02 1 Tile Study Raster Amber

Notice that both ARC-01 entries remain, carrying quantities 3 and 2. Both receive Arc Study, Vector and Teal. The metadata has enriched those entries without combining them.

The formula deliberately looks up one metadata field at a time before joining the results. This makes the output shape explicit: five inventory rows, two original columns and three added columns.

Use three lookup columns in Excel 2021 for Mac

For Excel 2021, work directly beside the original inventory. Keep the match-count check in column C, then enter Title, Format and Palette in D1:F1. Leave D2:F6 empty.

Enter this formula in Inventory!D2:

=XLOOKUP(A2:A6,Metadata!A2:A4,Metadata!B2:B4,"MISSING METADATA",0)

Enter this formula in Inventory!E2:

=XLOOKUP(A2:A6,Metadata!A2:A4,Metadata!C2:C4,"MISSING METADATA",0)

Enter this formula in Inventory!F2:

=XLOOKUP(A2:A6,Metadata!A2:A4,Metadata!D2:D4,"MISSING METADATA",0)

Press Return after each formula. Each result fills five rows beneath its starting cell. Your asset IDs and quantities remain in columns A:B, the diagnostic count remains in C, and the added metadata appears in D:F. Excel 2021 for Mac includes the dynamic-array behavior needed for this approach. What’s new in Excel 2021 for Mac

Adapt the workbook for more inventory rows

The examples use fixed ranges so every reference is visible. When your inventory grows, extend every inventory reference to the same last row. For example, if the inventory ends on row 501, the combined formula needs Inventory!A2:B501 and three instances of Inventory!A2:A501. Extend the match-count check to the same row.

When metadata grows, extend all metadata references together. If its last row becomes 101, use Metadata!A2:A101 for the keys and the corresponding B2:B101, C2:C101 and D2:D101 return ranges. Include actual records rather than adding a large block of unused rows.

For a workbook that grows regularly, consider converting the source ranges to Excel tables and using structured references. Microsoft documents that these references adjust as source rows are added or removed. Keep the spilled output in the worksheet grid outside any table. Dynamic array formulas and spilled array behavior

If you need enrichment inside an Excel table, use a separate single-result formula for each metadata column and inventory row. Tables support calculated columns that apply a formula throughout the column; a multi-cell spill belongs outside the table. Overview of Excel tables

Diagnose problems and verify the finished result

If Excel displays #SPILL!, inspect the intended destination cells. Move any existing content you need to retain, and ensure the formula is outside an Excel table. Only the top-left cell of a spilled result is editable; change the formula there. Microsoft documents these spill boundaries and obstruction errors. Dynamic array formulas and spilled array behavior

If a row displays MISSING METADATA, return to its match-count check. A zero means you need to add the missing metadata record or correct the asset ID. Check for accidental leading or trailing spaces when visually identical labels do not behave as expected; Microsoft identifies these as a source of unexpected COUNTIF results. Use the COUNTIF function in Microsoft Excel

Keep the two source worksheets in the same workbook for this setup. If you later move the source to another workbook, account for Microsoft’s documented limitation: linked dynamic arrays require both workbooks to be open, and refreshing with the source closed can produce #REF!. Dynamic array formulas and spilled array behavior

Verify the synthetic example against three concrete checks:

  1. There are five output data rows in the original inventory order.
  2. The quantities remain 3, 5, 2, 4, 1, totaling 15.
  3. Repeated IDs receive identical metadata, and every match-count check is 1.

These checks establish the intended outcome: every inventory entry retains its place and quantity while receiving the description belonging to its asset ID. For a larger workbook, repeat the same checks after extending ranges or adding metadata records.

Sources