A lookup that returns #N/A (or a different “not found” error) is often working as designed: the lookup value does not exactly match any candidate under that function’s rules. Before hiding the error with IFERROR, determine whether the problem is the value, the table, or the search mode.
Start with a known-good exact-match example
Create the following values in LibreOffice Calc. The cell names below specify their actual locations, so there is no ambiguity about the formula’s range.
| Cell | Product code (A) | Description (B) | Price (C) |
|---|---|---|---|
| Row 2 | P-101 | Notebook | 9.50 |
| Row 3 | P-102 | Pencil set | 4.25 |
| Row 4 | P-104 | Stapler | 18.50 |
| Row 5 | P-105 | Tape | 3.75 |
Enter P-104 into E2. In F2, enter:
=VLOOKUP(E2;$A$2:$C$5;3;0)
Expected result: 18.50. The third argument asks for the third column of the specified range, not column C in every workbook. The final 0 requires an exact match; leaving that argument out can select a nearby sorted-range value. If the result is formatted as currency, the display may include a currency symbol.
Download the simple lookup practice CSV to recreate the source table. Import it into a new test sheet and retain product codes as text.
This example is independently reasoned from the provided table and the LibreOffice VLOOKUP definition; it has not been executed in a spreadsheet application for this guide.
Six things to check, in order
| Symptom | Likely cause | Practical test |
|---|---|---|
| A nearby product is returned instead of an exact one | Approximate-match mode | Force exact matching and check sorted-range behavior |
| Codes look identical but don’t match | Leading/trailing or invisible spaces | Compare LEN results; inspect a plain-text copy |
| Numbers have the same appearance but one side is text | Data-type mismatch | Use ISNUMBER on both the lookup and the source cell |
| A match is visible but the lookup still fails | Wrong first column or range | Confirm the searched key is in the function’s designated lookup area |
| A lookup works until copied downward | Relative reference shifted | Use $A$2:$C$5 or other appropriate absolute references |
| The first or last matching row surprises you | Duplicate keys or match-order rules | Count duplicate keys before choosing a specific result |
1. Show the actual characters
Suppose E2 is the five-character string P-104 but A4 contains P-104 with a trailing space. Their visible labels may seem identical, but LEN(E2) is 5 while LEN(A4) is 6. Removing or standardizing the extraneous space in a separate helper column makes the mismatch visible before replacing the original IDs. TRIM may remove ordinary spaces; imported nonbreaking spaces can require a different cleanup step.
2. Don’t convert identifiers to numbers
If one source stores 00123 as text but the other stores numeric 123, those are not interchangeable identifiers. Converting both to numeric values might produce a lookup result while destroying meaningful leading zeros. Decide whether the field is truly a quantity or an identifier before conversion. See Why numbers stored as text matter.
3. Make the matching mode explicit
LibreOffice’s VLOOKUP help documents sorted-range/approximate behavior when the optional last argument is omitted. Zoho Sheet’s MATCH documentation has its own match-type definitions. Don’t transplant a last argument from one program into another without reading its documentation.
Avoid masking the real problem
You can wrap a lookup with an error handler after debugging. But returning an empty string for every error makes a broken range, invalid column index or missing source look like a genuinely missing product. A better approach is to keep the raw lookup visible during investigation, then use a specific user-facing fallback only after the underlying result is understood.
Next step by software
- LibreOffice VLOOKUP, XLOOKUP and MATCH
- Apple Numbers XLOOKUP — Numbers uses its own table-reference model
- PlanMaker XLOOKUP — check release and matching arguments
- Formula Migration Helper — compare documented alternatives before moving a workbook
Verification note: Official function documentation was checked. The table and 18.50 outcome were independently reasoned, not executed in LibreOffice Calc, Apple Numbers, Zoho Sheet or PlanMaker. No application screenshots are used.