Spreadsheets

Return "Uncategorised" instead of #N/A in a lookup formula

Return Uncategorised instead of #N/A in a lookup: when IFNA is safer than IFERROR, and how to filter the leftover rows for a quick review.

Editorial illustration for Return "Uncategorised" instead of #N/A in a lookup formula

Wrap the lookup in IFNA and give it your fallback text: =IFNA(VLOOKUP(B2,$F$2:$G$200,2,FALSE),"Uncategorised"). IFNA replaces only the #N/A that a lookup returns when the merchant is not in your table, so every other error still shows up. IFERROR also works, but it swallows every kind of error, which is the reason to prefer IFNA for categorising.

Why does a lookup return #N/A in the first place?

#N/A means "not available": the formula looked for the narration in your merchant table and found no exact match. That is normal for a new merchant, so it is useful information, not a fault. You want to turn it into a visible label rather than a red error that breaks SUM and SUMIFS further along.

Check the fourth argument of VLOOKUP first. FALSE means exact match. If you leave it out, VLOOKUP assumes TRUE, uses an approximate match on a sorted list, and can quietly return the wrong category instead of #N/A. For text lookups, always type FALSE.

IFNA or IFERROR: which should I use?

IFERROR catches every error type. That sounds helpful until something real goes wrong. Say you delete column G of the merchant table by mistake. Every lookup now returns #REF!. With IFERROR, all rows quietly turn into "Uncategorised" and the sheet looks normal. With IFNA, the #REF! stays visible and you fix it the same day.

ErrorUsual causeIFNA catches it?IFERROR catches it?
#N/AMerchant missing from tableYesYes
#REF!Column deleted or index too largeNo, which is goodYes, hiding a real fault
#NAME?Function name mistypedNo, which is goodYes, hiding a real fault
#VALUE!Wrong kind of inputNo, which is goodYes, hiding a real fault
#DIV/0!Division by a blank or zeroNoYes

IFNA exists in Excel 2013 and later and in Google Sheets. Use IFERROR only when you truly want any problem replaced, such as a presentation cell where a blank looks neater than an error.

What if I use XLOOKUP or INDEX MATCH?

XLOOKUP, in Excel 365, Excel 2021 and Google Sheets, has a built-in fallback, so no wrapper is needed:

  • =XLOOKUP(B2,$F$2:$F$200,$G$2:$G$200,"Uncategorised")
  • =IFNA(INDEX($G$2:$G$200,MATCH(B2,$F$2:$F$200,0)),"Uncategorised")
  • =IFNA(VLOOKUP(TRIM(B2),$F$2:$G$200,2,FALSE),"Uncategorised") when the narration may have stray spaces

How do I review only the Uncategorised rows?

The whole point of a visible fallback is to filter on it. Three quick ways:

  1. Switch on the filter in the header row (Data, then Create a filter in Sheets, or Filter in Excel), open the dropdown on column C and keep only "Uncategorised".
  2. In Excel 365, spill them to a side area: =FILTER(A2:D500,C2:C500="Uncategorised","Nothing to review").
  3. In Google Sheets, the same idea needs a wrapper because FILTER has no empty-result argument: =IFNA(FILTER(A2:D500,C2:C500="Uncategorised"),"Nothing to review").

Add a counter at the top, =COUNTIF($C$2:$C$500,"Uncategorised"), so you can see the backlog shrink as you add merchants to the table. A worked month: say 120 transactions arrive and the counter shows 18. You add 6 new merchants to the table, a few of which cover several rows each, and the counter drops to 4. Those four are one-off payments you categorise by hand.

Before you rely on it

  • Type the fallback text identically everywhere, including the same capital U
  • Make sure the merchant table has no duplicate merchants with different categories, since lookups take the first one
  • Rerun the review filter after each statement paste
  • If many rows stay unmatched because of spaces or odd characters, clean the narration with TRIM before the lookup

When the merchant table becomes a chore, SpendixAI can take over the matching: you upload the statement, check the rows before saving, and your corrections are remembered next time.

SPENDIXAI JOURNAL

Useful ideas start with a clearer picture of your own spending.

Explore your reports