To join repeated asset labels without multiplying rows, number each label’s occurrences in both lists, then look up the label-and-occurrence pair. This Excel for Mac walkthrough preserves every left-hand row and flags missing matches.

What does matching by occurrence mean?

Suppose two art-asset lists both contain several rows labeled Arc Tile. Your task is to attach the first source quantity to the first left-hand Arc Tile, the second source quantity to the second, and so on. Other labels can appear between those rows; the occurrence count belongs to each label separately.

A label-only relationship is ambiguous. If one list contains three Arc Tile rows and the other contains two, pairing every matching combination would produce six combinations for that label. A plain lookup has another limitation: Microsoft documents that XLOOKUP returns the first match it finds. Repeating a label-only lookup therefore does not advance through successive occurrences. XLOOKUP function

The solution is to make the lookup key more specific: Arc Tile|1, Arc Tile|2, and Arc Tile|3. Each numbered key identifies one occurrence. The method below is a worked construction using documented Excel functions; Microsoft’s documentation supplies the function behavior.

Use this approach when row order defines the pairing. If the rows should instead match by a separate asset identifier or revision field, use that information. Occurrence numbers cannot determine which two records belong together when the order carries no meaning.

Which Excel for Mac versions support this method?

The main formulas use COUNTIF, concatenation with &, and XLOOKUP. Microsoft lists Excel for Microsoft 365 for Mac, Excel 2024 for Mac and Excel 2021 for Mac on the relevant function pages. Its XLOOKUP documentation explicitly says that the function is unavailable in Excel 2016 and Excel 2019. XLOOKUP function, Use the COUNTIF function in Microsoft Excel

Start with the filled-down lookup below. A later variation returns the same seven results with one spilling array formula. Microsoft documents that spilled formulas belong in the worksheet grid outside Excel Tables. Dynamic array formulas and spilled array behavior

Set up two synthetic asset lists

Use a blank worksheet in one workbook. Enter the following left-hand list in A1:B8. These labels and quantities are illustrative; the left quantity remains part of the original list and is not used to decide the match.

Asset label Left quantity
Arc Tile 4
Dot Panel 2
Arc Tile 6
Line Print 3
Dot Panel 5
Arc Tile 1
Ring Card 2

Enter the source list in G1:H7. Keep the row order shown so your results match the walkthrough.

Asset label Source quantity
Dot Panel 12
Arc Tile 8
Line Print 7
Arc Tile 9
Dot Panel 0
Grid Sheet 11

Add the headers Occurrence, Join key and Matched source quantity in C1, D1 and E1. Add Occurrence, Join key and Used by left list in I1, J1 and K1. Leave column F empty to separate the lists visually.

For this example, use nonblank text labels shorter than 255 characters, with consistent capitalization and spacing and no wildcard characters such as *, ? or ~. Enter a numeric source quantity for every source row, including zero where appropriate. These prerequisites keep the simple formulas’ interpretation unambiguous.

Number occurrences and build the lookup keys

Count each label from the top of its own list

Enter this formula in C2, press Return, and copy it down through C8:

=COUNTIF($A$2:A2,A2)

The first endpoint, $A$2, stays fixed. The second endpoint expands as you copy downward. At each row, the formula counts how often that row’s label has appeared so far. COUNTIF counts cells meeting a supplied criterion. Use the COUNTIF function in Microsoft Excel

The resulting left-hand occurrence numbers should be 1, 1, 2, 1, 2, 3, 1. In particular, the third Arc Tile receives 3 even though other labels separate its rows.

Enter the corresponding source formula in I2, then copy it through I7:

=COUNTIF($G$2:G2,G2)

The source occurrence numbers should be 1, 1, 1, 2, 2, 1. Count the two lists independently: a row’s occurrence number is its position among matching labels within its own list.

Combine the label and occurrence

Enter this formula in D2 and copy it through D8:

=A2&"|"&C2

Enter this formula in J2 and copy it through J7:

=G2&"|"&I2

The left list now contains Arc Tile|1, Arc Tile|2 and Arc Tile|3. The source contains only the first two of those keys. This exposes the missing third occurrence before any quantities are returned.

Return one source quantity per left-hand row

Enter the following formula in E2, then copy it through E8:

=XLOOKUP(D2,$J$2:$J$7,$H$2:$H$7,"Unmatched",0)

The formula searches the source join keys, returns the corresponding source quantity, and displays Unmatched when that numbered key is absent. The final 0 explicitly requests an exact match, which is also XLOOKUP’s default. XLOOKUP function

Your completed left-hand result should be:

Asset label Left quantity Occurrence Join key Matched source quantity
Arc Tile 4 1 Arc Tile|1 8
Dot Panel 2 1 Dot Panel|1 12
Arc Tile 6 2 Arc Tile|2 9
Line Print 3 1 Line Print|1 7
Dot Panel 5 2 Dot Panel|2 0
Arc Tile 1 3 Arc Tile|3 Unmatched
Ring Card 2 1 Ring Card|1 Unmatched

There are still seven left-hand rows. The second Arc Tile receives 9, rather than repeating the first occurrence’s 8. The third has no source partner. Ring Card also has no partner, while the matched zero for the second Dot Panel remains visible.

Keep Unmatched distinct from zero. In this example, zero is a real source quantity; using zero for an absent match would hide that difference. Likewise, resolve blank source quantities before interpreting the output so an empty source cell is not mistaken for a recorded quantity.

Optional: return the results as one array

After checking the filled-down results, clear only E2:E8 and enter this alternative in E2:

=XLOOKUP(D2:D8,$J$2:$J$7,$H$2:$H$7,"Unmatched",0)

This supplies all seven left join keys as the lookup input. With the sample data, the result occupies E2:E8 in left-list order. Press Return; the adjacent output cells must be empty. Microsoft describes this automatic placement of array results as spilling. Dynamic array formulas and spilled array behavior

Choose the filled-down version if you prefer an independently inspectable formula on every row. Choose the spilling version if you prefer one formula controlling the result column. Both rely on the same occurrence keys.

Check missing matches and unused source rows

Preserving the left row count does not mean every source row was used. Add a reverse check so extra source occurrences remain visible. In K2, enter the following and copy it through K7:

=COUNTIF($D$2:$D$8,J2)

For this sample, the first five source rows return 1, and Grid Sheet|1 returns 0. That zero identifies a source row with no left-hand partner. A value greater than 1 would mean a source key is referenced by multiple left keys and needs investigation.

Use these checks together:

  • Confirm that the output occupies exactly the seven original left-hand rows.
  • Confirm the quantity sequence: 8, 12, 9, 7, 0, Unmatched, Unmatched.
  • Confirm that the third Arc Tile and Ring Card remain visibly unmatched.
  • Confirm that Grid Sheet|1 is marked unused in the source audit.

The expected sequence and unused-source result were checked with a small deterministic calculation of the occurrence-pairing logic. The Excel formulas themselves were inspected against Microsoft’s documented syntax; they were not executed in Excel for Mac.

Troubleshoot the result before extending the workbook

Pairings change after sorting

The occurrence formulas use current row order. Sorting either list and recalculating can change which repeated row receives occurrence 1 or 2. Decide the intended order before numbering. If a permanent pairing is required, retain a stable identifier that records it instead of relying solely on a recalculated position.

Labels look alike but match unexpectedly

Microsoft notes that COUNTIF ignores case, recognizes wildcard criteria, and can give unexpected results with leading or trailing spaces or other unwanted characters. It also documents limitations when matching strings longer than 255 characters. Standardize labels in both lists before numbering them; the short, consistently formatted labels above avoid those conditions. Use the COUNTIF function in Microsoft Excel

The array formula shows #SPILL!

Check for existing content in E3:E8, including the earlier filled-down formulas. Move anything you need to retain, then clear the obstruction. If the formula is inside an Excel Table, use the filled-down approach or move the spilling output outside the Table. These are documented spill restrictions. Dynamic array formulas and spilled array behavior

You need a lookup without XLOOKUP

The same helper keys can drive an INDEX/MATCH lookup. As an alternative to either formula above, enter this in E2 and copy it through E8:

=INDEX($H$2:$H$7,MATCH(D2,$J$2:$J$7,0))

Here, MATCH with 0 locates the exact join key, and INDEX returns the quantity at that position. Missing keys deliberately remain visible as #N/A. Microsoft documents both functions for current Mac editions. This scalar formula needs no spilling output. MATCH function, INDEX function

New rows are absent from the results

The walkthrough uses fixed ranges. When adding rows, extend the occurrence and join-key formulas, the source lookup and return ranges together, and the reverse audit range. For the spilling version, also extend the left lookup input beyond D8. Adding data below these ranges alone does not include it in the calculation.

Once the output sequence and reverse audit agree with the intended order, you have a usable occurrence-based join: each left-hand asset row keeps its place, receives at most one source quantity, and retains a visible indication when its matching occurrence is absent.

Sources