by In this tutorial, we will be looking at how to use IFERROR and VLOOKUP functions together to trap and handle different errors. In addition, you are going to learn how to do sequential vlookups in Excel by nesting multiple IFERROR functions one onto another.
Excel VLOOKUP and IFERROR - these two functions may be pretty hard to understand separately, let alone when they are combined. In this article, you will find a few easy-to-follow examples that address common uses and clearly illustrate the formulas' logic.
If you don't have much experience with IFERROR and VLOOKUP functions, it may be a good idea to revise their basics first by following the above links.
IFERROR VLOOKUP formula to handle #N/A and other errors
When Excel Vlookup fails to find a lookup value, it throws an #N/A error, like this:
Depending on your business needs, you may want to disguise the error with your own text, zero, or a blank cell.
Example 1. IFERROR with VLOOKUP formula to replace errors with your own text
If you'd like to replace the standard error notation with your custom text, wrap your VLOOKUP formula in IFERROR, and type any text you want in the 2nd argument (value_if_error), for example "Not found":
With the lookup value in B2 in the Main table and the lookup range A2:B4 in the Lookup table, the formula takes the following shape:
=IFERROR(VLOOKUP(B2,'Lookup table'!$A$2:$B$5, 2, FALSE), "Not found")
The screenshot below shows our Excel IFERROR VLOOKUP formula in action:
The result looks much more understandable and far less intimidating, isn't it?
In a similar manner, you can use INDEX MATCH together with IFERROR:
=IFERROR(INDEX('Lookup table'!$B$2:$B$5,MATCH(B2,'Lookup table'!$A$2:$A$5,0)), "Not found")
The IFERROR INDEX MATCH formula is especially useful when you want to pull values from a column that lies to the left of the lookup column (left lookup), and return your own text when nothing is found.
Example 2. IFERROR with VLOOKUP to return blank or 0 if nothing is found
If you don't want to show anything when the lookup value is not found, have IFERROR display an empty string (""):
In our example, the formula goes as follows:
=IFERROR(VLOOKUP(B2,'Lookup table'!$A$2:$B$5, 2, FALSE), "")
As you can see, it returns nothing when the lookup value is not in the search list.
If you'd like to replace the error with the zero value, put 0 in the last argument:
=IFERROR(VLOOKUP(B2,'Lookup table'!$A$2:$B$5, 2, FALSE), 0)
Word of caution! Excel IFERROR function catches all kinds of errors, not only #N/A. Is it good or bad? All depends on your goal. If you want to mask all possible errors, IFERROR Vlookup is the way to go. But it may be an unwise technique in many situations.
For example, if you've created a named range for your table data, and misspelled that name in your Vlookup formula, IFERROR will catch a #NAME? error and replace it with "Not found" or any other text you supply. As the result, you may never know your formula is delivering wrong results unless you spot the typo yourself. In such a case, a more reasonable approach would be trapping only #N/A errors. For this, use IFNA Vlookup formula in Excel 2013 and higher, IF ISNA VLOOKUP in all Excel versions.
The bottom line is: be very careful when choosing a companion for your VLOOKUP formula :)
Nest IFERROR within VLOOKUP to always find something
Imagine the following situation: you look up a specific value in a list and do not find it. What choices do you have? Either get an N/A error or show your own message. Actually, there is a third option - if your primary vlookup stumbles, then search for something else that is definitely there!
Taking our example further, let's create some sort of dashboard for our users that will show them an extension number of a specific office. Something like this:
So, how do you pull the extension from column B based on the office number in D2? With this regular Vlookup formula:
=VLOOKUP($D$2,$A$2:$B$7,2,FALSE)
And it will work nicely as long as your users enter a valid number in D2. But what if a user inputs some number that does not exist? In this case, let them call the central office! For this, you embed the above formula in the value argument of IFERROR, and put another Vlookup in the value_if_error argument.
The complete formula is a bit long, but it works perfectly:
=IFERROR(VLOOKUP("office "&$D$2,$A$2:$B$7,2,FALSE),VLOOKUP("central office",$A$2:$B$7,2,FALSE))
If the office number is found, the user gets the corresponding extension number:
If the office number is not found, the central office extension is displayed:
To make the formula a bit more compact, you can use a different approach:
First, check if the number in D2 is present in the lookup column (please notice that we set col_index_num to 1 for the formula to look up and return value from column A): VLOOKUP(D2,$A$2:$B$7,1,FALSE)
If the specified office number is not found, then we search for the string "central office", which is definitely in the lookup list. For this, you wrap the first VLOOKUP in IFERROR and nest this whole combination inside another VLOOKUP function:
=VLOOKUP(IFERROR(VLOOKUP(D2,$A$2:$B$7,1,FALSE),"central office"),$A$2:$B$7,2)
Well, a slightly different formula, the same result:
But what is the reason to look up "central office", you may ask me. Why not supply the extension number directly in IFERROR? Because the extension may change at some point in the future. If this happens, you will have to update your data just once in the source table, without worrying about updating each of your VLOOKUP formulas.
How to do sequential VLOOKUPs in Excel
In situations when you need to perform the so-called sequential or chained Vlookups in Excel depending on whether a prior lookup succeeded or failed, nest two or more IFERROR functions to run your Vlookups one by one:
The formula works with the following logic:
If the first VLOOKUP does not find anything, the first IFERROR traps an error and runs another VLOOKUP. If the second VLOOKUP fails, the second IFERROR catches an error and runs the third VLOOKUP, and so on. If all Vlookups stumble, the last IFERROR returns your message.
This nested IFERROR formula is especially useful when you have to Vlookup across multiple sheets as shown in the below example.
Let's say, you have three lists of homogeneous data in three different worksheets (office numbers in this example), and you want to get an extension for a certain number.
Assuming the lookup value is in cell A2 in the current sheet, and the lookup range is A2:B5 in 3 different worksheets (North, South and West), the following formula works a treat:
=IFERROR(VLOOKUP(A2,North!$A$2:$B$5,2,FALSE), IFERROR(VLOOKUP(A2,South!$A$2:$B$5,2,FALSE), IFERROR(VLOOKUP(A2,West!$A$2:$B$5,2,FALSE),"Not found")))
So, our "chained Vlookups" formula searches in three different sheets in the order we nested them in the formula, and brings the first match it finds:
You can find more examples in this article: VLOOKUP in multiple sheets in Excel.
This is how you use IFERROR with VLOOKUP in Excel. I thank you for reading and hope to see you on our blog next week!
89 comments
hi can you help i did do this
=IF(ISNA(VLOOKUP(B15,Table2_1,2,FALSE)),"",VLOOKUP(B15,Table2_1,2,FALSE))
when i did add the table or add data to it, it will not show anything
Hi! This formula works and finds the correct value. The problem is not with the formula, but with your data.
I recommend reading this guide: Excel VLOOKUP not working - fixing errors
You can also write this formula as recommended in the article above:
=IFERROR(VLOOKUP(B15,Table2_1,2,FALSE),"")
=IFERROR(VLOOKUP(F175,RAW!A176:S46495,4,FALSE),"-") its generate black data help me fix
I can't understand your formula and check it, as I don't have your data. I can't guess what result you wanted to get.
Hi! How would I go about this with a match function for multiple sheets? This is the formula that I have:
=IFERROR(IF(MATCH(B2,Sheet1!B:B,0), “Yes”,),”No”)
I’m able to pull from Sheet1 with no issues. But how do I nest sheet2, sheet3, sheet4 into this formula.
Thank you for your help!!
Hello Aaron!
To search for values on multiple worksheets, you can use the methods suggested in these instructions: Vlookup across multiple sheets with IFERROR. Instead of VLOOKUP, use MATCH. For example:
=IFERROR(IF(MATCH(B2,Sheet1!B:B,0), "Yes",), IFERROR(IF(MATCH(B2,Sheet2!B:B,0), "Yes",),"No"))
Awesome. It worked. Thank you!
How would you apply IFERROR logic to this formula that is XLOOKUP-ing "X1","X2","X3" .... "X10" in an array that may not have one or more of these Xs?
=IF(XLOOKUP("P",CC9:QI9,CC7:QI7)>XLOOKUP("X1",CC9:QI9,CC7:QI7),"No P before X1",IF(XLOOKUP("X10",CC9:QI9,CC7:QI7)-1<=XLOOKUP("X9",CC9:QI9,CC7:QI7),"No P between X9 & X10",IF(XLOOKUP("X9",CC9:QI9,CC7:QI7)-1<=XLOOKUP("X8",CC9:QI9,CC7:QI7),"No P between X8 & X9",IF(XLOOKUP("X8",CC9:QI9,CC7:QI7)-1<=XLOOKUP("X7",CC9:QI9,CC7:QI7),"No P between X7 & X8",IF(XLOOKUP("X7",CC9:QI9,CC7:QI7)-1<=XLOOKUP("X6",CC9:QI9,CC7:QI7),"No P between X6 & X7",IF(XLOOKUP("X6",CC9:QI9,CC7:QI7)-1<=XLOOKUP("X5",CC9:QI9,CC7:QI7),"No P between X5 & X6",IF(XLOOKUP("X5",CC9:QI9,CC7:QI7)-1<=XLOOKUP("X4",CC9:QI9,CC7:QI7),"No P between X4 & X5",IF(XLOOKUP("X4",CC9:QI9,CC7:QI7)-1<=XLOOKUP("X3",CC9:QI9,CC7:QI7),"No P between X3 & X4",IF(XLOOKUP("X3",CC9:QI9,CC7:QI7)-1<=XLOOKUP("X2",CC9:QI9,CC7:QI7),"No P between X2 & X3",IF(XLOOKUP("X2",CC9:QI9,CC7:QI7)-1<=XLOOKUP("X1",CC9:QI9,CC7:QI7),"No P between X1 & X2","OK"))))))))))
Hi! If I understand your task correctly, use the IFERROR function as described in the article above and in this manual: How to use IFERROR in Excel with formula examples.
The formula might look something like the following:
IFERROR([your_formula],"Not found")
Hi There, I tried applying the IFERROR Formula, but I keep getting the "too few arguments" error. I'm vlooking up 3 cells and multiplying them by a value. The formula works w/o IFERROR, but with IFERROR I get lost.
IFERROR Formula
=IFERROR((((VLOOKUP('Sep 2023 '!I14,'Currency Exchange'!$A$7:$B$9,2,FALSE)*J14)+IFERROR(VLOOKUP('Sep 2023 '!K14,'Currency Exchange'!$A$7:$B$9,2,FALSE)*L14)+IFERROR(VLOOKUP('Sep 2023 '!M14,'Currency Exchange'!$A$7:$B$9,2,FALSE),"0")*N14))))
Original Formula:
=(VLOOKUP('Sep 2023 '!I16,'Currency Exchange'!$A$7:$B$9,2,FALSE)*J16)+(VLOOKUP('Sep 2023 '!K16,'Currency Exchange'!$A$7:$B$9,2,FALSE)*L16)+(VLOOKUP('Sep 2023 '!M16,'Currency Exchange'!$A$7:$B$9,2,FALSE)*N16)
Hi! You can find the examples and detailed instructions here: How to use IFERROR in Excel with formula examples. You have not added a value_if_error argument to your formula. The formula might look like this:
=IFERROR((VLOOKUP('Sep 2023 '!I16,'Currency Exchange'!$A$7:$B$9,2,FALSE)*J16)+(VLOOKUP('Sep 2023 '!K16,'Currency Exchange'!$A$7:$B$9,2,FALSE)*L16)+(VLOOKUP('Sep 2023 '!M16,'Currency Exchange'!$A$7:$B$9,2,FALSE)*N16), "Not found")
=IFERROR(VLOOKUP(AC4,'All Process Order'!C:O,13,0),VLOOKUP(AE4,'All Process Order'!C:O,13,0))
how to get 0 or blank
Hi! I don't have your data, so I can't understand your formula and check it. Your question is not entirely clear, please specify.
=VLOOKUP(IFERROR(VLOOKUP(G3,$A$1:$B$7,1,FALSE),"central office"),$A$1:$B$7,2??)
Question! why didn't you add false at the end of the vlookup formula? can you explain me when i should add that option in my formula or there is no need?
Hi! Read carefully this guide: Excel VLOOKUP function tutorial with formula examples. Pay attention to the VLOOKUP syntax.