Excel and Google Sheets how-to
Hide #N/A in VLOOKUP with IFERROR
Your order list has a code that is not in the price list, and VLOOKUP shows #N/A. IFERROR puts your own words there instead.
| A | B | C | D | E | |
|---|---|---|---|---|---|
| 1 | Code | Price | Order | Price | |
| 2 | A100 | 2.50 | Z999 | Not found | |
| 3 | A200 | 4.00 | B300 | 10.00 | |
| 4 | B300 | 10.00 | A100 | 2.50 | |
| 5 | B400 | 7.25 | C500 | 3.75 | |
| 6 | C500 | 3.75 | A200 | Not found |
Sample data. Price list in A:B, order codes in D, the formula in E2 is copied down. D6 has a space after A200.
The formula
Click the first Price cell under Order (E2) and type:
=IFERROR(VLOOKUP(D2,A:B,2,FALSE),"Not found")E2 shows Not found: Z999 is not in the price list, so VLOOKUP alone would show #N/A. Copy it down and B300 shows 10.00, A100 2.50 and C500 3.75.
- VLOOKUP(D2,A:B,2,FALSE) looks for D2 in the first column of A:B and brings back the 2nd column, the price.
- FALSE means an exact match only.
- IFERROR(…,"Not found") shows your words when the lookup fails, and the price when it works.
A blank or 0 instead
Two quotes with nothing between them leave the cell empty:
=IFERROR(VLOOKUP(D2,A:B,2,FALSE),"")Use 0 when you multiply the price by a quantity:
=IFERROR(VLOOKUP(D2,A:B,2,FALSE),0)*2That gives 0 for Z999. The blank version times 2 gives #VALUE!, because "" is text. A total of the column still works with words or blanks in it: =SUM(E2:E6) gives 16.25 (10 + 2.50 + 3.75), because SUM skips text.
IFNA: hide only #N/A
IFERROR hides every error, including your own mistakes. Ask for column 3 of a two column range by accident:
=IFERROR(VLOOKUP(D3,A:B,3,FALSE),"Not found")It shows Not found, although B300 is in the list. IFNA only replaces #N/A, the "not in the list" error:
=IFNA(VLOOKUP(D3,A:B,3,FALSE),"Not found")Now the cell shows an error (#REF! in Excel), so you can see and fix the column number. With the right column, =IFNA(VLOOKUP(D2,A:B,2,FALSE),"Not found") shows Not found for Z999, just like IFERROR.
The code is in the list but shows Not found
D6 holds "A200 " with a space at the end, so the exact match fails and E6 shows Not found. TRIM removes spaces at the ends before the lookup:
=IFERROR(VLOOKUP(TRIM(D6),A:B,2,FALSE),"Not found")E6 now shows 4.00. Codes copied from emails, PDFs and other systems often carry these spaces.
Numbers stored as text
When the price list has number codes like 1002 but the order cell holds 1002 as text (it sits on the left of the cell, often after an import), Excel's exact match fails with #N/A. VALUE turns the text into a number first:
=VLOOKUP(VALUE(D2),A:B,2,FALSE)The other way round, a list of codes stored as text and a number to find, add an empty text to the number:
=VLOOKUP(D2&"",A:B,2,FALSE)Both find the match. =ISNUMBER(D2) shows TRUE for a real number and FALSE for text.
Always end with FALSE
Without FALSE, VLOOKUP looks for the closest match in a sorted list and can bring back the wrong price with no error at all:
=VLOOKUP("B350",A2:B6,2)That gives 10.00, the price of B300, although B350 is not in the list. IFERROR cannot catch it, because there is no error. With FALSE:
=IFERROR(VLOOKUP("B350",A2:B6,2,FALSE),"Not found")shows Not found. VLOOKUP, IFERROR, IFNA, TRIM and VALUE work in every current version of Excel and in Google Sheets. In Excel 2021 and later, Microsoft 365 and Google Sheets, XLOOKUP has its own "not found" part and matches exactly by default.
Templates with the math already done
Rather skip the formulas? These templates come with every formula built and checked, plus a filled-in sample.
- Inventory SpreadsheetList your items once, log stock in and out, and see what to reorder.
- Invoice Template and TrackerPick an invoice number and a print-ready invoice fills itself in.
- Reseller Inventory and Profit TrackerEvery item from purchase to sale, with profit per item.
More how-tos
- Find a price by its code with XLOOKUPType a product code, get its price back from your price list. One formula, no helper columns.
- Total each category with SUMIFSAdd up all the Fuel, all the Office, all the Meals: one formula per category, and more rules when you need them.
- Check two conditions at once with IF and ANDFree shipping only when the order is 50 or more and ships in Canada: one IF, one AND, done.
- Add leading zeros to numbersType 00021 and Excel or Google Sheets shows 21, because to them it is just the number 21. Here is how to get the zeros back, and keep them.
- Fix #DIV/0! in an averageAn average of nothing is a division by zero, so AVERAGE shows #DIV/0! until the first number arrives. One IF fixes it.
- Count rows that meet two conditions with COUNTIFSHow many of Ana's deals were 500 or more? COUNTIFS answers it in one cell, and takes as many rules as you need.
- Calculate percentage changeSales went from 400 to 500. That is a 25% increase, and one short formula works it out for every row.
- Calculate years of service from a start dateAna started on 15 March 2019. How many full years has she worked? DATEDIF counts them, and the same formula works out an age from a date of birth.
- Combine first and last name into one cellFirst names in one column, last names in the next, and you need "Ana Lopez" in one cell. The & sign joins them.
- Find duplicates in a list with COUNTIFA mailing list where some people signed up twice. One formula shows how many times each email appears, so the repeats stand out.
- Split a full name into first and last nameYour list has "Ana Lopez" in one cell and you need Ana and Lopez in two. Two short formulas find the space and split there.
- Total amounts by month with SUMIFSA list of dated expenses and you want one total per month. SUMIFS adds every amount between the first and the last day of the month.
- Count working days between two datesAna is off from 1 Oct to 14 Oct. How many working days is that? NETWORKDAYS counts Monday to Friday and leaves out the weekends.
- Count cells that contain textA stock list where some items have a note and most do not. One COUNTIF with an asterisk counts the notes and skips the empty cells.
- Free pricing calculatorsPrice a job from your real costs and margin. No sign up.