Apple Numbers formulas.
D2How Apple Numbers differs from Excel
Apple Numbers supports most of the same functions as Excel — but a few key differences trip people up when they paste an Excel formula and it does not work:
- No XLOOKUP. Use VLOOKUP or INDEX/MATCH instead.
- No QUERY. Filter with built-in filters or combine IF/INDEX/MATCH.
- No Excel-style dynamic-array spilling. Functions like UNIQUE/FILTER behave differently or are unavailable.
- Table-based references. A Numbers sheet is made of separate tables; references are table-aware (and use header names) rather than a single A1 grid.
The good news: the everyday functions — VLOOKUP, IF, SUMIF, SUMIFS, COUNTIF, INDEX, MATCH, AVERAGE — all work with the same names and arguments as Excel.
Common formulas in Numbers
Lookup a value (VLOOKUP)
=VLOOKUP(E1, A:B, 2, FALSE)Searches column A for the value in E1 and returns the matching value from column B. FALSE forces an exact match.
Lookup in any direction (INDEX/MATCH)
=INDEX(B:B, MATCH(E1, A:A, 0))Because Numbers has no XLOOKUP, this is the go-to for looking up to the left.
Conditional value (IF)
=IF(B2>100, "High", "Low")Conditional sum (SUMIF / SUMIFS)
=SUMIF(A:A, "West", B:B)=SUMIFS(C:C, A:A, "January", B:B, "West")Count with a condition (COUNTIF)
=COUNTIF(D:D, ">100")Tips for working with Numbers references
- Click cells to insert references — Numbers writes them table-aware automatically.
- If a table has a header row, you can reference columns by their header name, which makes formulas easier to read.
- To reference another table, click into it while building the formula.
Generate Numbers-safe formulas with FormulaAI
Pick Apple Numbers as your platform in FormulaAI, describe what you need, and it returns a formula that works in Numbers — automatically avoiding unsupported functions like XLOOKUP and QUERY, and offering INDEX/MATCH where Excel would use XLOOKUP.
Frequently asked questions
Does Apple Numbers use the same formulas as Excel?
Mostly yes. Numbers supports the same core functions as Excel — VLOOKUP, IF, SUMIF, SUMIFS, COUNTIF, INDEX, MATCH, AVERAGE — with the same names and arguments. The main differences are that Numbers does not have XLOOKUP, QUERY, or Excel-style dynamic-array spilling, and it references cells through tables rather than a single grid.
How are cell references different in Apple Numbers?
Numbers organizes a sheet into separate tables, and formulas reference cells by clicking them, which produces table-aware references (and header names when headers exist) rather than plain A1-style addresses. The function arguments are the same; only the way references are written differs.
Does Apple Numbers have XLOOKUP?
No. XLOOKUP is not available in Apple Numbers. Use VLOOKUP for left-to-right lookups, or INDEX and MATCH together for lookups in any direction — both are fully supported in Numbers.
How do I do a VLOOKUP in Numbers?
Use the same syntax as Excel: =VLOOKUP(search_value, range, column_index, FALSE). Select the lookup value, the table range, and the column to return; FALSE forces an exact match. For left lookups, use INDEX/MATCH instead.
Can FormulaAI generate Numbers formulas?
Yes. Choose Apple Numbers as the target in FormulaAI and it returns formulas that work in Numbers — automatically avoiding functions Numbers does not support, such as XLOOKUP or QUERY.
Related guides
- VLOOKUP Guide — works the same in Numbers.
- INDEX MATCH Guide — the XLOOKUP alternative for Numbers.
- IF Formula Guide — conditional logic.
- FAQ — more about FormulaAI.