Fix the #CALC! error in FILTER
=FILTER(A2:E11,D2:D11=H1,"No matches")Excel · result on the sample sheet: No matches
Describe what you need and get a working IFNA formula for Excel and Google Sheets, tested on real data before you copy it. IFNA returns a fallback only when a formula results in #N/A, the error lookups give when a value is missing.
Each formula below was calculated on its own sample sheet. Open one to see the sheet, why it works, and to try it yourself.
=FILTER(A2:E11,D2:D11=H1,"No matches")Excel · result on the sample sheet: No matches
=IFNA(VLOOKUP(A2,$E$2:$F$5,2,FALSE),0)*B2Excel · result on the sample sheet: 5 rows (C2:C6)
=XLOOKUP(H1,A2:A6,B2:B6,XLOOKUP(H1,D2:D5,E2:E5,"Not found"))Excel · result on the sample sheet: $44.50
=IFS(B2<0,"Overdue",B2<=1,"Urgent",B2<=7,"This week",TRUE,"Later")Excel · result on the sample sheet: 6 rows (C2:C7)
=XLOOKUP(C2,$A$2:$A$9,$B$2:$B$9,"")Excel · result on the sample sheet: 8 rows (D2:D9)
=XLOOKUP(E2:E6,A2:A9,C2:C9,"Not found")Excel · result on the sample sheet: 5 rows (F2:F6)
=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)
=FILTER(A2:D10,ISNUMBER(SEARCH(G1,A2:A10&" "&B2:B10&" "&C2:C10)),"No results")Excel · result on the sample sheet: 4 rows (F3:I6)
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.
IFNA returns a fallback only when a formula results in #N/A, the error lookups give when a value is missing.
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.