Comparing the displayed spelling
A test such as B2="#N/A" checks a string and can propagate a genuine error. Test its error type instead.
Classify real errors without misreading text
Read-only
Step-by-step guide · read-only ·
Loading your interactive spreadsheet…
Preparing the worksheet and formulas…
Data & tools
Task requirements
Enter one formula in G5. For all twelve values in B2:B13, return a twelve-row, two-column report. The first column is Missing for genuine #N/A, Error for the other six classic Excel errors, and Value for every other value. The second column is the documented error code 1 through 7 for a genuine error and numeric zero otherwise. Keep literal error-looking text, Boolean values and blanks in the Value category. Do not add headings.
Edit a source value and its type
Text that looks like an error and a genuine error have different types. Apply an explicit type to the current worksheet, then check the complete report again.
The draft matches the current worksheet value and type.
Follow the highlighted cells. Switch to Your turn to edit. Answer cells: G5:H16. Enter one formula in G5; keep the rest of the output area clear. Browser calculations may differ from your Excel version.
Selection & formula search
Practice stays on this device. No worksheet data is uploaded.
Notes & examples
About this lesson
Return a category and classic error code for each imported value while preserving source order.
THE QUICK ANSWER01
Return a category and classic error code for each imported value while preserving source order.
ƒx
=HSTACK(IF(ISNA(B2:B13),"Missing",IF(ISERR(B2:B13),"Error","Value")),IFERROR(ERROR.TYPE(B2:B13),0))| G | H |
|---|---|
| "Error" | 1 |
| "Error" | 2 |
| "Error" | 3 |
| "Error" | 4 |
| "Error" | 5 |
| "Error" | 6 |
| "Missing" | 7 |
| "Value" | 0 |
| "Value" | 0 |
| "Value" | 0 |
| "Value" | 0 |
| "Value" | 0 |
The remaining reserved output cells are blank. Keep that area clear when trying the formula.
The formula uses the interactive worksheet above. Adjust ranges and criteria for your own data.
How it works
Error values carry a type
The first imported #NULL! is a real error with code 1. A later literal "#N/A" is text, so its row is Value and zero. The same displayed spelling is not enough to choose a category.
A code describes the error, not its cause
Codes 1–7 identify #NULL!, #DIV/0!, #VALUE!, #REF!, #NAME?, #NUM! and #N/A. A code alone does not tell you which reference or input must be repaired.
Supported teaching sources
The twelve inputs accept finite numbers within ±1,000,000, text within 80 characters, Boolean values, blanks and the seven classic typed errors. Modern spill, calculation and connected-service errors require their own diagnostic rules. Use the explicit type editor to compare text with a genuine error.
Read the full step-by-step walkthrough
- Read the actual cell type
An error value and a string with the same spelling are different inputs. The source worksheet retains this type information.
- Separate missing values
ISNA identifies genuine #N/A. The literal text "#N/A" is not a missing-value error.
- Keep other errors distinct
ISERR includes the other classic errors and excludes #N/A. Ordinary values receive Value.
- Attach a diagnostic number
ERROR.TYPE reports the documented error code. IFERROR changes its non-error #N/A result to numeric zero.
- Keep both aligned columns
HSTACK joins the category and code at each original source position.
Things to watch
Using ISERROR for every category
ISERROR includes #N/A, so it cannot by itself distinguish Missing from the other Error rows.
About this example
Original typed teaching inputs, independent source/type rules and complete error/spill checks. Native Excel and Sheets recalculation/edit/save remain separate verification steps.
Content version 1. Practice checks compare calculated results across different datasets; they do not certify every possible formula.