How to fix a lookup that returns #N/A
A lookup that returns #N/A has not found its value. Learn the usual causes and the fix for each one.
Task 1Why a lookup returns #N/A2 questions
#N/A means the lookup value was not found. Often the value looks right to you, but it is not the same as the one in the table.
- A space after the value: P02 followed by a space is not P02
- A number stored as text: the text 2143 is not the number 2143
- A typing slip, such as a letter O in place of a zero
| A | B | C | |
|---|---|---|---|
| 1 | Invoice | Customer | Amount |
| 2 | 2141 | Hollin School | 186.00 |
| 3 | 2142 | Moss Lane Vets | 412.50 |
| 4 | 2143 | Quay Dental | 95.40 |
| 5 | 2144 | Tarn Garage | 268.80 |
The invoice numbers in column A are numbers. =VLOOKUP(2143,A2:C5,2,FALSE) finds invoice 2143, but =VLOOKUP("2143",A2:C5,2,FALSE) looks for a piece of text and returns #N/A.
Task 2Three fixes4 questions
Task 3A clear message when the value is missing3 questions