Use IFNA around a spilled lookup to fill its #N/A results from a second reference table. This Excel for Mac example matches art-asset labels, preserves quantities already found in the primary table, and clearly marks labels missing from both tables.

Which Excel for Mac versions support this approach?

Use Excel for Microsoft 365 for Mac, Excel 2024 for Mac, or Excel 2021 for Mac. Microsoft lists these editions for both XLOOKUP and IFNA. Its XLOOKUP documentation explicitly excludes Excel 2016 and Excel 2019, despite those editions appearing in the page’s broader applicability list. The formulas below target the supported Mac editions. XLOOKUP function, IFNA function

The task is specific: you have one column of requested asset labels and want one quantity per label. The primary reference takes precedence; the second reference supplies quantities only where the primary lookup produces #N/A. All labels and quantities below are synthetic.

Set up two reference tables and a requested-asset list

Start on a blank worksheet in one workbook. Use the cell locations below so the formulas work without changing their references. These small reference tables are ordinary worksheet ranges; you do not need to convert them into Excel Table objects.

Enter the primary reference in D1:E4:

Cell in column D Asset label Cell in column E Quantity
D1 Asset E1 Quantity
D2 Leaf outline E2 8
D3 Dot texture E3 0
D4 Tile pattern E4 12

Enter the second reference in G1:H5:

Cell in column G Asset label Cell in column H Quantity
G1 Asset H1 Quantity
G2 Dot texture H2 9
G3 Curve sketch H3 5
G4 Grain texture H4 3
G5 Leaf outline H5 10

Enter Requested asset in J1, then put these five labels in J2:J6, one per cell and in this order:

  1. Leaf outline
  2. Dot texture
  3. Curve sketch
  4. Grain texture
  5. Arc motif

Keep quantities numeric and populate every quantity cell in the two references. For this example, zero means a recorded quantity of zero. It does not mean that the asset is missing.

Create the spilled list of labels

Enter the headings Asset, Primary quantity, and Completed quantity in A1, B1, and C1. Leave A2:C20 empty, then enter this formula in A2:

=FILTER(J2:J20,J2:J20<>"","")

This filters out blank cells in the requested list. With the supplied data, it spills the five asset labels into A2:A6. Microsoft documents FILTER for the three Mac editions listed above and explains that its final argument supplies a result when no rows meet the condition. Here, that result is an empty string. FILTER function

The later formulas use A2# to reference this entire spill. The # operator follows the spill’s size, so it avoids hard-coding the current endpoint as A6. Microsoft describes this behavior in its spilled range operator documentation.

Fill the missing quantities from the second reference

First, create the primary lookup in B2:

=XLOOKUP(A2#,$D$2:$D$4,$E$2:$E$4)

This searches the primary asset column and returns the corresponding quantities. XLOOKUP uses exact matching by default and returns #N/A when no match exists and no alternative result is supplied. XLOOKUP function

For the sample, B2:B6 should contain 8, 0, #N/A, #N/A, and #N/A. Keep those missing-match errors available for the next step.

Now enter the completing formula in C2:

=IFNA(B2#,XLOOKUP(A2#,$G$2:$G$5,$H$2:$H$5,"Not listed"))

IFNA examines the primary spill. At positions containing #N/A, the formula supplies the corresponding result from the second lookup. Other primary results pass through unchanged. Microsoft specifically documents that IFNA returns an array of results when its first argument is an array formula. IFNA function

The expected worksheet result is:

Asset in column A Primary quantity in column B Completed quantity in column C
Leaf outline 8 8
Dot texture 0 0
Curve sketch #N/A 5
Grain texture #N/A 3
Arc motif #N/A Not listed

Read these rows as a precedence test. Leaf outline remains 8 even though the second reference contains 10. Dot texture remains 0 even though the second reference contains 9. Curve sketch and Grain texture receive their secondary quantities. Arc motif stays visibly unresolved because neither reference contains it.

The two arrays must describe the same requested assets in the same order. In this setup, both originate from A2#. If you adapt an existing workbook, do not combine a primary result sorted one way with a fallback lookup based on a differently ordered label list.

Use one formula when you do not need the intermediate column

To keep the primary lookup and completion together, replace the formula in C2 with this complete alternative:

=IFNA(
    XLOOKUP(A2#,$D$2:$D$4,$E$2:$E$4),
    XLOOKUP(A2#,$G$2:$G$5,$H$2:$H$5,"Not listed")
)

This uses the same sample data and should produce the same five completed quantities. Column B is no longer required by the calculation. Keeping the two-stage version while setting up the workbook makes it easier to see which reference supplies each result.

Decide whether a result is actually missing

Before adapting the formula, inspect what the first lookup returns. A missing label, an empty-looking result, and a quantity of zero require different decisions.

Primary result What this completion formula does What to check
Numeric quantity, including 0 Preserves it Confirm it is the intended primary quantity.
#N/A Uses the secondary lookup Confirm the error represents an absent label.
Empty string, "" Preserves it Inspect whether the primary formula hides missing matches.
Another error Leaves it visible Repair the underlying formula or reference.

For example, if your existing primary lookup uses "" as its if_not_found argument, IFNA cannot recognize that empty string as #N/A. Remove that argument from the primary lookup, as shown in B2, or use the combined formula above. The completion logic depends on retaining a recognizable missing-match result. XLOOKUP function, IFNA function

Also inspect the reference quantities themselves. IFNA responds to an error value; it does not test whether a quantity cell was populated. If a matched primary record has an unfinished quantity, complete that record or define a separate rule for unfinished quantities before applying this workflow.

There is one further distinction: an existing #N/A error inside the primary quantity data will also trigger this fallback. For this example, the prerequisite is clean numeric quantity columns, with #N/A arising only from an unsuccessful lookup. Check that condition before treating every replacement as a missing asset match.

Check precedence and keep the spill working

Use the sample’s five rows as a small acceptance check before substituting your own asset labels. Together, they test a primary match, a valid zero, two secondary matches, and a label absent from both references.

For an additional check, temporarily change the secondary quantity for Leaf outline from 10 to 14. The completed quantity should remain 8. Then change the secondary Curve sketch quantity from 5 to 6; its completed quantity should become 6. Restore the original values afterward. These are illustrative checks for your workbook, not reported Excel test results.

Keep one intended record per asset label in each reference. XLOOKUP normally searches from the first item and returns the first match, so repeated labels should not be expected to combine quantities. Resolve duplicate-label meaning before relying on this lookup design. XLOOKUP function

Fix a blocked output range

Enter each spilling formula only in its starting cell. Excel fills the neighboring cells automatically, and only the top-left cell of a spill is editable. Place the formulas outside Excel Table objects; Microsoft supports tables as source data but does not support spilled formulas inside them. Dynamic array formulas and spilled array behavior

If you see #SPILL!, select the formula cell to reveal the intended output boundary. Use the error alert’s Select Obstructing Cells option, where available, and move any existing entries that block the result. Merged cells can also prevent spilling; move the output or unmerge the affected cells. How to correct a #SPILL! error

Expand the inputs deliberately

The requested-list formula examines J2:J20. Labels added within that range are included automatically; labels entered below it require expanding the formula’s range. Likewise, the reference lookups use fixed ranges ending at rows 4 and 5. Extend each asset range and its matching quantity range together when adding records.

For a larger worksheet, Excel Tables with structured references can make source ranges adjust as rows are added or removed. Keep the output formulas in the worksheet grid outside those tables. Dynamic array formulas and spilled array behavior

Keep this example’s inputs in the same workbook. Microsoft documents limited dynamic-array support between workbooks: linked dynamic arrays require both workbooks to remain open, and refreshing after closing the source can produce #REF!. Dynamic array formulas and spilled array behavior

Once the expected rows agree, use the completed column as your asset-quantity result. Retain the primary-first rule, preserve meaningful zeros, and leave labels absent from both references marked Not listed so you can finish the reference data deliberately.

Sources