Use INDEX with two exact-match MATCH functions to return the quantity where an asset row meets a format column. This guide builds a small art-asset worksheet, explains the formula, and shows a nested XLOOKUP alternative for supported Mac versions.

Which lookup method works in Excel for Mac?

For this task, you have an asset name, such as Dot Grid, and a format, such as PNG. You want the single quantity at their intersection. This is commonly called a two-way lookup.

Microsoft lists INDEX and MATCH as available in Excel for Microsoft 365 for Mac, Excel 2024 for Mac, and Excel 2021 for Mac. Both methods in this article work within that documented Mac scope. See Microsoft’s INDEX function documentation and MATCH function documentation.

XLOOKUP is also documented for those Mac editions. Microsoft explicitly says it is unavailable in Excel 2016 and Excel 2019, despite those editions appearing in the page’s broader applicability list. If your edition lacks XLOOKUP, use the INDEX and MATCH method. Microsoft: XLOOKUP function

Start with INDEX and MATCH if you want to inspect the row and column positions separately while learning. Choose nested XLOOKUP if you already use that function and prefer its default exact matching.

Set up a small art-asset quantity grid

In a blank worksheet, enter the following synthetic data in A1:D5. The leftmost table column below represents worksheet column A; do not add an extra column for row numbers. These quantities are illustrative.

Asset SVG PNG JPG
Tile Arc 12 18 6
Dot Grid 8 24 10
Loop Ribbon 15 9 0
Soft Square 7 14 5

Each asset occupies one row, and each format occupies one column. For this example, use unique asset labels and unique format headers. Enter an explicit numeric zero where the quantity is known to be zero; leave unresolved quantities for checking before using the grid.

Create a separate lookup area on the same worksheet:

Cell Enter
F1 Asset to find
F2 Dot Grid
G1 Format to find
G2 PNG
H1 Quantity

The three source ranges have different jobs: A2:A5 contains asset labels, B1:D1 contains format headers, and B2:D5 contains only quantities. Keeping the headings outside the quantity range makes the relative positions line up correctly.

Return the quantity with INDEX and two MATCH functions

Select H2, enter this complete formula, and press Return:

=INDEX($B$2:$D$5,MATCH(F2,$A$2:$A$5,0),MATCH(G2,$B$1:$D$1,0))

The result should be 24: the quantity for Dot Grid in the PNG column. Microsoft’s Mac instructions describe selecting a result cell, entering a formula beginning with =, and pressing Return. Microsoft: Enter a formula

Understand the two positions

The first MATCH finds the asset’s position within A2:A5; the second finds the format’s position within B1:D1. The final 0 requests exact matching. Without that argument, MATCH defaults to approximate matching with an ordering requirement. Microsoft: MATCH function

For the supplied inputs, the two inner calculations are:

=MATCH(F2,$A$2:$A$5,0)

Result: 2.

=MATCH(G2,$B$1:$D$1,0)

Result: 2.

The full formula therefore selects row 2, column 2 within the quantity block:

=INDEX($B$2:$D$5,2,2)

That is worksheet cell C3, containing 24. INDEX uses positions within its supplied range, so its second row is worksheet row 3 here. Microsoft documents that supplying both positions returns their intersecting value. Microsoft: INDEX function

Keep the source ranges fixed when copying

The dollar signs lock the source ranges. The input references F2 and G2 remain relative, so copying H2 down to H3 makes the next formula read F3 and G3 while using the same source grid. On Mac, you can also select a reference in the formula bar and press Command+T to cycle reference types. Microsoft: Switch between relative and absolute references

For example, enter Tile Arc in F3 and JPG in G3, then copy H2 into H3. The new result should be 6. This gives you a small request list without changing the original asset grid.

Use nested XLOOKUP in supported Mac versions

With the same worksheet and lookup inputs, you can instead enter this formula in H2:

=XLOOKUP(F2,$A$2:$A$5,XLOOKUP(G2,$B$1:$D$1,$B$2:$D$5))

This returns 24 for Dot Grid and PNG. Microsoft’s documentation includes a nested XLOOKUP example that performs both a vertical and horizontal lookup to return an intersection. Exact matching is the default. Microsoft: XLOOKUP function

In this worked example, the inner lookup selects the PNG quantity column: 18, 24, 9, and 14. The outer lookup selects the entry aligned with Dot Grid. Keeping the same three source ranges makes it straightforward to compare this method with the earlier formula.

Check the result before using a larger grid

Use the following cases as a small acceptance check. Change F2 and G2 for each row, then compare H2 with the expected result. Restore Dot Grid and PNG afterward if you want to keep the original example.

Asset input Format input Expected result What it checks
Dot Grid PNG 24 Middle intersection
Tile Arc JPG 6 First asset and last format
Soft Square SVG 7 Last asset and first format
Loop Ribbon JPG 0 Explicit zero quantity
Tile Arcs PNG #N/A Missing asset label
Dot Grid TIFF #N/A Missing format header

If a number is wrong, inspect the range boundaries before changing the lookup method. For this grid, A2:A5 must align with the four rows of B2:D5, and B1:D1 must align with its three columns. Including A1 in only the asset lookup range, for example, shifts the calculated position away from the intended quantity row.

When extending this particular grid with another asset in row 6, update both the asset range to A2:A6 and the quantity block to B2:D6. When adding a format in column E, update both the header range to B1:E1 and the quantity block to B2:E5. These paired edits preserve the layout used by the formulas.

Duplicate labels also need attention. Exact-match MATCH returns the first matching position and does not distinguish uppercase from lowercase; it also interprets * and ? as wildcards in text searches. Use distinct labels without those characters for this setup. Microsoft: MATCH function

Show a useful message for a missing label

Once the basic lookup works, you can replace a missing-match error with a readable message:

=IFNA(INDEX($B$2:$D$5,MATCH(F2,$A$2:$A$5,0),MATCH(G2,$B$1:$D$1,0)),"Check asset or format")

IFNA returns your message when the enclosed formula produces #N/A; otherwise it keeps the result. It is documented for Microsoft 365 for Mac, Excel 2024 for Mac, and Excel 2021 for Mac. Microsoft: IFNA function

In this example, Tile Arcs produces Check asset or format, while Loop Ribbon with JPG still returns 0. That distinction keeps a missing label separate from a recorded zero quantity. If a source quantity itself contains #N/A, this wrapper will replace that error too, so inspect the source cell when the labels appear correct.

For everyday use, keep asset names unique, preserve the alignment of the three source ranges, and check a few known intersections after changing the grid. You can then retrieve each requested art-asset quantity by changing the asset and format inputs.

Sources