XLOOKUP If Not Found: Stop Showing #N/A in Excel (Fourth Argument)
An XLOOKUP that cannot find a value shows #N/A, which looks broken and breaks any sum beneath it. Learn the fourth argument of XLOOKUP, so missing values show a plain message or a blank instead, on a small project list in Excel.
Try it yourself
Copy these tables into a blank sheet, then follow the steps below.
Paste at cell A1:
| Code | Project | Owner |
|---|---|---|
| PRJ-01 | New website | Ana |
| PRJ-02 | Payroll upgrade | Ben |
| PRJ-03 | Warehouse move | Chris |
| PRJ-04 | Data cleanup | Dana |
Paste at cell E1:
| Request | Code | Owner | Or blank |
|---|---|---|---|
| CR-1 | PRJ-02 | ||
| CR-2 | PRJ-09 | ||
| CR-3 | PRJ-04 | ||
| CR-4 | PRJ-07 |
Step by step
- XLOOKUP not found. When XLOOKUP can't find a value, it shows #N/A. Fill a column down and a few #N/A errors make the sheet look broken. They also break any sum beneath them. XLOOKUP has a fourth argument just for this.
- The project list. The project list is in A1:C5. Each project code in column A has a name in column B and an owner in column C.
- The requests. Beside it, E1:H5 holds 4 change requests. Each one has a project code in column F, and we want that project's owner next to it.
- Plain XLOOKUP. In G2, type =XLOOKUP(F2,$A$2:$A$5,$C$2:$C$5). It looks for the code from F2 in the code column and returns the owner from column C. The $ signs lock the ranges so they don't move. Fill it down to G5.
G2
=XLOOKUP(F2,$A$2:$A$5,$C$2:$C$5) - The #N/A problem. 2 of the codes aren't in the project list, so 2 cells show #N/A, starting with G3. A manager reading this would think the sheet is broken.
- If not found. Now add the fourth argument. After the return range, type a comma and then the message in quotes: =XLOOKUP(F2,$A$2:$A$5,$C$2:$C$5,"Not found"). Fill it down again. The 2 missing codes now say Not found, in plain words.
G2
=XLOOKUP(F2,$A$2:$A$5,$C$2:$C$5,"Not found") - Or a blank. Prefer an empty cell? In H2, use two quote marks with nothing between them as the fourth argument: =XLOOKUP(F2,$A$2:$A$5,$C$2:$C$5,""). Missing codes then show nothing, and a SUM below them still works. Fill that down to H5 too.
H2
=XLOOKUP(F2,$A$2:$A$5,$C$2:$C$5,"") - It fixes itself. Now correct the code in F3 to PRJ-01. The owner appears straight away, because the formula looks the code up again by itself.
F3
PRJ-01 - The fourth part. XLOOKUP's fourth argument sets what to show when nothing is found. Give it a plain message or a blank, and never send out a sheet full of #N/A.
Common mistakes
- Leaving out the quotes around the message. Type ,Not found without quotes and Excel reads it as a name it doesn't know, so you get #NAME?. Text must sit inside quote marks, like "Not found".
- Wrapping XLOOKUP in IFERROR instead. IFERROR hides every error, even one caused by a broken formula or a bad range. The fourth argument only covers values that aren't found, so real mistakes still show.
- Expecting the fourth argument to fix 0 results. If the code is found but its owner cell is empty, XLOOKUP returns 0, not #N/A, so the fourth argument doesn't apply. That is a gap in the source list, so fill it in there.
- Filling down without $ signs. Without $A$2:$A$5 and $C$2:$C$5, the ranges move down one row each time you fill. Lower rows then miss part of the list and show Not found for codes that do exist.
When a business analyst uses this
In a change request log, each request carries a project code and the owners are kept in a project list. =XLOOKUP(F2,$A$2:$A$5,$C$2:$C$5,"Not found") fills in each owner, and every Not found points to a mistyped or unknown project code to sort out before the change board meets.
Questions people ask
How do I return a blank if XLOOKUP finds nothing?
Use two quote marks with nothing between them as the fourth argument, like =XLOOKUP(F2,$A$2:$A$5,$C$2:$C$5,""). The cell looks empty instead of showing #N/A.
Can XLOOKUP return 0 or my own text when a value isn't found?
Yes. The fourth argument can be any value: 0 with no quotes for a number, or text in quotes such as "Not found" or "No owner".
Can the if-not-found argument be another XLOOKUP?
Yes. Put a second XLOOKUP in the fourth argument so it searches a second list whenever the first one finds nothing. You can give that second XLOOKUP its own fourth argument as well.
