Logical
Flagging a lookup that missed
Definition
Flagging a missed lookup means wrapping that lookup in ISERROR.
Syntax
=ISERROR(VLOOKUP(lookup_value, table_array, col_index_num, FALSE))
Arguments
| Argument | Meaning |
|---|---|
lookup_value |
The key to find, such as a code in C2. |
table_array |
The table to search. The key column must come first. |
col_index_num |
The column of the table to return, for example 2. |
Worked example
A parts list stores codes and counts in A2:B3. Cell C2 holds a code that is not on the list.
| A: Code | B: Count | C: Seek |
|---|---|---|
| N-1 | 3 | N-9 |
| N-2 | 5 |
Formula: =ISERROR(VLOOKUP(C2,A2:B3,2,FALSE))
Result: TRUE. N-9 is not in the first column, so the lookup errors and ISERROR is true.
Practice
Cell A2 holds Z-99. Cells A3:A4 hold Z-10 and Z-20. Cells B3:B4 hold 6 and 9. In C2, write a formula that looks up A2 in that table and shows 1 when the lookup misses.
- In C2, write an IF formula that uses ISERROR and shows 1 when an exact-match VLOOKUP of A2 in A3:B4 is an error, and 0 otherwise.
- C2 should show the result for a missing Z-99.
Step 1 of 2
Enter =IF(ISERROR(VLOOKUP(A2,A3:B4,2,FALSE)),1,0) in C2.
Z-99 is not in A3:A4, so VLOOKUP is an error and the result is 1.
Formulas here use English function names, such as SUM, and a period as the decimal separator, in every language.
Loading the sheet…
C2 uses ISERROR.
C2 now shows 1.
The next lesson is Counting weekdays between two dates.
Related functions
ISERROR is the function that turns a missed lookup into a true result.
Common mistake
Testing the key itself, as in =ISERROR(C2): N-9 is text, not an error, so the result is FALSE instead of TRUE.