# IFNA formula generator

> Describe what you need and get a IFNA formula for Excel and Google Sheets, run on real data before you copy it. Plus 8 tested IFNA examples.

Source: https://spreadsheetpractice.com/tools/formula-generator/ifna · Updated October 29, 2026 · Formulas and results on this page were run by the Spreadsheet Practice formula engine on November 20, 2026

IFNA returns a fallback only when a formula results in #N/A, the error lookups give when a value is missing.

Describe the task at [IFNA formula generator](https://spreadsheetpractice.com/tools/formula-generator/ifna) and get a IFNA formula for Excel and Google Sheets, tested on real data.

## Tested IFNA examples

### Fix the #CALC! error in FILTER

`=FILTER(A2:E11,D2:D11=H1,"No matches")`

Excel. Result on the sample sheet: No matches. [See it and try it](https://spreadsheetpractice.com/formulas/fix-filter-calc-error)

### What is the difference between IFERROR and IFNA

`=IFNA(VLOOKUP(A2,$E$2:$F$5,2,FALSE),0)*B2`

Excel. Result on the sample sheet: 5 rows (C2:C6). [See it and try it](https://spreadsheetpractice.com/formulas/iferror-vs-ifna)

### Look up a value in two tables

`=XLOOKUP(H1,A2:A6,B2:B6,XLOOKUP(H1,D2:D5,E2:E5,"Not found"))`

Excel. Result on the sample sheet: $44.50. [See it and try it](https://spreadsheetpractice.com/formulas/lookup-in-two-tables)

### Why does IFS return #N/A when no condition is met

`=IFS(B2<0,"Overdue",B2<=1,"Urgent",B2<=7,"This week",TRUE,"Later")`

Excel. Result on the sample sheet: 6 rows (C2:C7). [See it and try it](https://spreadsheetpractice.com/formulas/ifs-returns-na)

### Look up a manager's name from the same employee table

`=XLOOKUP(C2,$A$2:$A$9,$B$2:$B$9,"")`

Excel. Result on the sample sheet: 8 rows (D2:D9). [See it and try it](https://spreadsheetpractice.com/formulas/manager-name-from-same-table)

### Look up a list of values at once

`=XLOOKUP(E2:E6,A2:A9,C2:C9,"Not found")`

Excel. Result on the sample sheet: 5 rows (F2:F6). [See it and try it](https://spreadsheetpractice.com/formulas/look-up-a-list-of-values-at-once)

### Use VLOOKUP with ARRAYFORMULA

`=ARRAYFORMULA(IF(B2:B9="","",IFNA(VLOOKUP(B2:B9,E2:F6,2,FALSE),"Not found")))`

Google Sheets. Result on the sample sheet: 8 rows (C2:C9). [See it and try it](https://spreadsheetpractice.com/formulas/vlookup-with-arrayformula)

### Make a search box

`=FILTER(A2:D10,ISNUMBER(SEARCH(G1,A2:A10&" "&B2:B10&" "&C2:C10)),"No results")`

Excel. Result on the sample sheet: 4 rows (F3:I6). [See it and try it](https://spreadsheetpractice.com/formulas/make-a-search-box)

## Questions

### What does the IFNA formula generator do?

You describe the task in plain English, and it writes the formula, using IFNA where that's the right tool, for your table or a sample one. The formula is run in our engine in Excel and Google Sheets mode before you see it, so you get its result, not just a guess.

### What does IFNA do?

IFNA returns a fallback only when a formula results in #N/A, the error lookups give when a value is missing.

### Can I learn to write IFNA formulas myself?

Yes. Each example below opens a page where you try the formula in a live grid and it's checked as you go, and the IFNA function page has a practice exercise.

---

Spreadsheet Practice (https://spreadsheetpractice.com) teaches spreadsheet formulas by practice: every exercise is graded in a live grid and re-checked on changed data.
