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 QUERY. Filter with built-in filters or combine IF/INDEX/MATCH.
- 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))Current versions of Numbers can also look to the left with XLOOKUP; INDEX/MATCH does the same and works in older versions too.
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 QUERY.
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 QUERY, 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?
Yes. Current versions of Numbers include XLOOKUP and XMATCH, with the same arguments as in Excel. VLOOKUP and INDEX/MATCH are fully supported too — the safer choice when the file is also opened in Excel 2019 or older, which has no XLOOKUP.
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 QUERY.
Related guides
- VLOOKUP Guide — works the same in Numbers.
- INDEX MATCH Guide — lookups in any direction.
- IF Formula Guide — conditional logic.
- FAQ — more about FormulaAI.