VLOOKUP returns #N/A: here's how to fix it

This is probably the most frequently asked spreadsheet question in the world: the customer really is in the list, but VLOOKUP still returns an error. In Euro-Office, this error is called #N/A (in Dutch Excel it's called #N/B). It means: not found. Below are the causes, from most to least common.

1. Hidden spaces

In our example, A3 contains "Kapsalon Knip " with a trailing space. To you it looks identical, but not to the spreadsheet. This often happens with data you've copied from a website, email or export.

Solution: use TRIM to remove the spaces first. =VLOOKUP(TRIM(A3),Facturen!$B$2:$H$13,6,FALSE) does find the customer.

If the list comes from a web page, it may contain "hard" spaces that TRIM doesn't recognise. In that case, use SUBSTITUTE(A3,CHAR(160)," ") and possibly CLEAN for invisible characters.

2. You're not doing an exact match

The fourth argument of VLOOKUP must be FALSE (or 0). If you leave it out, Euro-Office looks for an "approximate" match. This gives strange results or #N/A if the list isn't sorted.

3. The table shifts when you drag the formula

If you drag the formula downwards, B2:H13 shifts along to B3:H14 and the top customer falls outside the range. Add dollar signs: $B$2:$H$13. Press F4 while typing to add them quickly.

4. Number versus text

If you're searching by customer number or postcode, both sides need to be the same type of value. A number stored as text is left aligned in the cell, a real number is right aligned. Make both columns match using the number format (see Formatting numbers, dates and currency), or search as text: =VLOOKUP(A3&"",...).

5. The lookup column isn't on the left

VLOOKUP only searches the first column of the range. If you want to search by customer name and it's in column B, the range must start at B (B2:H13, not A2:H13). If the answer is to the left of the lookup column, use XLOOKUP instead: see XLOOKUP: look up to the left and with a custom message.

A neat message instead of #N/A

Does the value genuinely not exist, for example due to a typo? Catch the error: =IFNA(VLOOKUP(A4,Facturen!$B$2:$H$13,6,FALSE),"Not found"). With a typo such as "Kapsalon Knipp", you'll then see "Not found".

Tip: test whether the value itself is the problem with =MATCH(A3,Facturen!$B$2:$B$13,0). If that also returns #N/A, the issue lies in the values (spaces, text or number), not in your formula.