Excel Formula Generator
Pick what you want to do in plain English, fill in your cells, and get a formula you can paste, with each part explained and a note on which Excel versions and Google Sheets it works in. Switch to Explain mode to break down a formula you already have.
Usually a cell, like the product ID in F2.
One column. Add a sheet name for another tab, e.g. Prices!A2:A100.
Same rows as the lookup range.
Leave blank to show #N/A instead.
Built in your browser. Nothing you type is sent or saved.
Your formula
=XLOOKUP(F2, A2:A100, C2:C100, "Not found")=IFERROR(INDEX(C2:C100, MATCH(F2, A2:A100, 0)), "Not found")What each part does
F2- The value to look for.
A2:A100- The column (or row) to search.
C2:C100- The column to bring an answer back from. It must cover the same rows.
"Not found"- What to show when there is no match. Without it, XLOOKUP shows #N/A.
(left out)- Match mode and search mode are optional. The defaults are exact match, searching first to last.
Needs Excel 2021 or later, or Microsoft 365. Not available in Excel 2016 or 2019, where it shows #NAME?. Switch to VLOOKUP or INDEX/MATCH if you or the people you share the file with use an older Excel. Google Sheets has XLOOKUP.
Google Sheets supports XLOOKUP with the same arguments, so the same formula works there.
Need something custom? Paste this into Claude
Formula request prompt
Asks for one formula that works in your version, a line-by-line explanation, and fixes for likely errors.
How to use the Excel formula helper
- Pick a task. Choose what you want in plain English, like “Add up values that match conditions” or “Look up a value from another table.”
- Fill in your cells. Each box starts with an example. Replace it with your own cell or range, like F2 or B2:B100. For another tab, add the sheet name: Prices!A2:A100. Type text conditions as you would say them; the tool adds the quote marks.
- Copy the formula. Click into the cell where you want the answer, paste, and press Enter. If the tip says to fill down, drag the small square at the cell’s bottom-right corner.
- Check the version note. XLOOKUP, FILTER, UNIQUE and SORT need Excel 2021 or Microsoft 365. IFS and TEXTJOIN need Excel 2019 or later. If the file goes to someone on an older Excel, use the older alternative shown under the formula.
- Stuck on something custom? Describe your columns and what you want in the prompt box, copy it, and paste it into Claude or ChatGPT.
Most-used Excel formulas
A cheat sheet of the formulas this tool builds. “All” means every current version of Excel plus Google Sheets. Rows marked * work differently in Google Sheets (see the table further down).
| Function | Use it to | Example | Works in |
|---|---|---|---|
| XLOOKUP | Look up a value and return a match from another column | =XLOOKUP(F2, A2:A100, C2:C100, "Not found") | Excel 2021+, 365, Sheets |
| VLOOKUP | Look up a value in the first column of a table | =VLOOKUP(F2, A2:C100, 3, FALSE) | All |
| INDEX/MATCH | Look up in any direction, in any Excel version | =INDEX(C2:C100, MATCH(F2, A2:A100, 0)) | All |
| SUMIFS | Add up values that meet conditions | =SUMIFS(D2:D100, B2:B100, "West") | All |
| COUNTIFS | Count rows that meet conditions | =COUNTIFS(B2:B100, "West", C2:C100, ">=500") | All |
| AVERAGEIFS | Average values that meet conditions | =AVERAGEIFS(D2:D100, B2:B100, "West") | All |
| IF | Show one result or another | =IF(B2>1000, "Over budget", "OK") | All |
| IFS | Pick from several results in order | =IFS(B2>=90, "A", B2>=80, "B", TRUE, "C") | Excel 2019+, 365, Sheets |
| IFERROR | Show a fallback instead of an error | =IFERROR(B2/C2, 0) | All |
| TRIM | Remove extra spaces | =TRIM(A2) | All |
| TEXTJOIN | Join cells with a separator | =TEXTJOIN(", ", TRUE, A2:C2) | Excel 2019+, 365, Sheets |
| LEFT / RIGHT / MID | Pull characters from text | =MID(A2, 4, 3) | All |
| DATEDIF | Years, months or days between dates | =DATEDIF(A2, B2, "M") | All |
| NETWORKDAYS | Working days between dates | =NETWORKDAYS(A2, B2, E2:E12) | All |
| EOMONTH | Last day of a month | =EOMONTH(A2, 0) | All |
| UNIQUE | List each value once | =UNIQUE(B2:B100) | Excel 2021+, 365, Sheets |
| FILTER | Return only matching rows | =FILTER(A2:D100, C2:C100="West", "No matches") | Excel 2021+, 365, Sheets* |
| SORT | Return a sorted copy of a range | =SORT(A2:D100, 2, -1) | Excel 2021+, 365, Sheets* |
Two more patterns from Microsoft’s own guides: percent change is =(C2-B2)/B2 formatted as a percentage, and a running total is =SUM($C$2:$C2) filled down a column.
Common Excel formula errors and how to fix them
| Error | What it usually means | How to fix it |
|---|---|---|
| #N/A | A lookup could not find the value you asked for. IFS also shows it when none of its tests are true. | Check the value really exists, with the same spelling and no extra spaces (TRIM helps). Watch for numbers stored as text. In VLOOKUP, make sure the last argument is FALSE. To show a message instead, use XLOOKUP’s if_not_found argument or wrap the formula in IFERROR. |
| #REF! | The formula points to a cell that is no longer valid. This usually happens after the rows, columns or sheet it referred to were deleted. | Undo the deletion if you can, or click into the formula and select the right range again. In VLOOKUP, check the column number is not bigger than the number of columns in your table. |
| #VALUE! | Something in the formula is the wrong kind of value, often text where Excel expects a number or a date. | Look for dates or numbers stored as text, and stray spaces. MID shows #VALUE! if the start position is less than 1, and TEXTJOIN shows it if the result would be over 32,767 characters. |
| #SPILL! | A formula that returns several results (such as UNIQUE, FILTER or SORT) cannot fill the cells it needs, usually because something is already in them. | Clear the cells below and to the right of the formula. Click the error icon next to the cell to see what is in the way. Merged cells and formulas inside an Excel table can also cause it. |
| #NAME? | Excel does not recognize something in the formula. | Check the function name is spelled correctly and that text is inside quotes. It also appears when your Excel version does not have the function, for example XLOOKUP in Excel 2019. Use the Explain mode above to check each function’s version. |
| #CALC! | In Excel, FILTER found no matching rows and you did not give it an if_empty value. | Add a third argument, like "No matches". In Google Sheets, wrap FILTER in IFERROR instead. |
| #DIV/0! | The formula divided by zero or by an empty cell. AVERAGEIFS shows it when no rows match. | Check the cell you divide by. If zero is expected sometimes, wrap the formula in IFERROR. |
These are the most common causes, not the only ones. In Excel, clicking the small warning icon next to an error cell usually shows more detail about what went wrong.
Excel vs Google Sheets differences
Most everyday formulas work the same in both. These are the ones worth knowing about before you copy a formula from one to the other.
| Function | Excel | Google Sheets |
|---|---|---|
| FILTER | FILTER(array, include, [if_empty]). No matches and no if_empty shows #CALC!. | FILTER(range, condition1, [condition2, ...]). No if_empty argument; no matches shows #N/A, so wrap it in IFERROR. |
| SORT | SORT(array, [sort_index], [sort_order], [by_col]). Order is 1 (ascending) or -1 (descending). | SORT(range, sort_column, is_ascending, ...). Order is TRUE or FALSE, and you can add more sort columns in the same formula. |
| XLOOKUP | Only in Excel 2021+ and Microsoft 365. | Supported, with the same arguments and mode codes. The lookup range must be a single row or column. |
| DATEDIF | Same six units: "Y", "M", "D", "MD", "YM", "YD". | Same units. If the answer shows as a date, change the cell format to Number. |
| UNIQUE, IFS, TEXTJOIN, SUMIFS, COUNTIFS, AVERAGEIFS, IFERROR, EOMONTH, NETWORKDAYS | IFS and TEXTJOIN need Excel 2019+; UNIQUE needs 2021+. | Available, with the same argument order for everyday use. |
More for Excel and finance
Want AI working inside your spreadsheet instead of in a separate chat? Read our guide to Claude for Excel, then follow the steps to install Claude for Excel. For bigger models and reporting, see Claude for finance, and for budgets and household spreadsheets, see Claude for personal finance.
Frequently asked questions
Is there a free AI Excel formula generator?
This page is free with no sign-up. It doesn’t call an AI model: you pick a task, fill in your cells, and it builds the formula from fixed templates in your browser, so the syntax is the same every time and nothing you type leaves your computer. For anything it doesn’t cover, AI assistants like Claude and ChatGPT can write a formula from a plain-English description. Use the prompt on this page, which asks for your Excel version and a line-by-line explanation, and test the result on a few rows before you rely on it.
XLOOKUP vs VLOOKUP: which should I use?
Use XLOOKUP if everyone who opens the file has Excel 2021, Microsoft 365 or Google Sheets. It does an exact match by default, can return a column to the left, and lets you set a “not found” message. VLOOKUP works in every version but only looks in the first column of a table, returns columns to the right, and defaults to an approximate match unless you type FALSE as the last argument. XLOOKUP isn’t available in Excel 2016 or 2019, so for shared files use INDEX/MATCH, which works everywhere and in any direction.
Why does my formula show #SPILL!?
Newer functions like UNIQUE, FILTER and SORT return several results that fill (spill into) the cells below and to the right. #SPILL! usually means something is already in those cells, even a space. Clear them and the results appear. Merged cells in the way, or typing the formula inside an Excel table, can also cause it.
How do I understand a formula someone else wrote?
Switch the tool above to “Explain a formula” and paste it in. It lists each function with a one-line description, its arguments and which Excel versions have it, plus the cells and ranges the formula reads. Read nested formulas from the inside out: the innermost function runs first. If it flags a function it doesn’t know, check the spelling, or ask Claude to explain the whole formula.
Do Excel formulas work in Google Sheets?
Most of them do, including SUMIFS, COUNTIFS, IF, IFERROR, XLOOKUP, UNIQUE and DATEDIF. The two that trip people up are FILTER and SORT. Sheets FILTER has no “if empty” argument and shows #N/A when nothing matches, so wrap it in IFERROR. Sheets SORT uses TRUE or FALSE for the order instead of 1 and -1. The generator shows a separate Google Sheets version whenever it’s different.