Excel track · Exercise 68 of 86 Paid
Read #N/A, #VALUE! and #REF!
Tell a missing match apart from a broken reference, and print that difference as words.
Advanced
Code and price table with one code the table does not hold
Read-only data · screen 1 of 2 · answer in B9
In B9, say missing when the code in A8 is not in the table, and found when it is.
| A | B | |
|---|---|---|
| 1 | Code | Price |
| 2 | A-1 | 10 |
| 3 | B-2 | 20 |
| 4 | C-3 | 30 |
| 5 | A-1 | 12 |
| 6 | B-2 | 22 |
| 7 | C-3 | 32 |
| 8 | Q-9 | |
| 9 | Missing or found |
In B9, ask the table for a third column and say out of range when that column does not exist, otherwise fine.
| A | B | |
|---|---|---|
| 1 | Code | Price |
| 2 | A-1 | 10 |
| 3 | B-2 | 20 |
| 4 | C-3 | 30 |
| 5 | A-1 | 12 |
| 6 | B-2 | 22 |
| 7 | C-3 | 32 |
| 8 | Q-9 | |
| 9 | Column check |
Reference answer
What it does
An error in Excel is a value sitting in a cell, so a formula can test it like a number. ISNA answers TRUE only for #N/A, the error a lookup or a match raises when nothing matched. ISERROR answers TRUE for any error at all. ISERR is the same test with #N/A taken out.
The first screen asks about Q-9, which the code column does not hold, so MATCH fails and ISNA prints missing. The second asks INDEX for a third column of A2:B7, and the table has two, so the call fails with #REF! and ISERROR prints out of range.
The three errors differ in kind. #N/A is a data question: the list does not hold the code. #REF! is a formula question: the range does not reach that far. #VALUE! is arithmetic handed text where it expected a number.
Syntax
=IF(ISNA(x),"missing","found"); =IF(ISERROR(x),"bad","ok")Worked example
On Code and price table with one code the table does not hold, =IF(ISNA(MATCH(A8,A2:A7,0)),"missing","found") returns missing.
=IF(ISNA(MATCH(A8,A2:A7,0)),"missing","found")Common errors
| Error | What it means and how to fix it |
|---|---|
#N/A | Nothing matched: the code in A8 is not in the list MATCH searched. |
#REF! | The reference lies outside the range, as a column number past the last column does. |
#VALUE! | Arithmetic was handed text, as dividing the word missing by zero does. |
At work
An import log that says which rows have no matching product code, so nobody scrolls a column of #N/A.