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 Sir.
This formula is giving me "false" in result . Kindly help.
=IF(Q14>0.99,(IFERROR(VLOOKUP(Q14,Sheet3!$G$8:$H$144,2,0),"")))
Thanks
Sahil
Hi! Your IF formula is missing the [value_if_false] argument. Try this formula:
=IF(Q14>0.99,IFERROR(VLOOKUP(Q14,Sheet3!$G$8:$H$144,2,0),""),"")
It worked . Thanks a lot
I want to do a lookup from a different sheet and add the data of multiple cells in sheet 1, suppose, sheet 1 has this entry, 201, now i want to look it up in sheet 2, and need to add all values are in front of it in the sheet one. Suppose, sheet 2, Column A has 201, column B , 22, Column c, 44, Column D 155.. I want to put all values at the same time in sheet one .
Hi! Use the INDEX MATCH function to search for a value. To get a row from a range using the INDEX function, read this article. For example,
=INDEX(B2:D10,MATCH(201,A2:A10,0),)
I am trying to duplicate the sequential VLOOKUP example but mine is not working.
=IFERROR(VLOOKUP(D10,CAR_CAP_MRC!$A:$K,2,0),IFERROR(VLOOKUP(D10,CAR_CAP_MRC!$A:$L,3,0),IFERROR(VLOOKUP(D10,CAR_CAP_MRC!$A:$L,4,0),IFERROR(VLOOKUP(D10,CAR_CAP_MRC!$A:$L,5,0),IFERROR(VLOOKUP(D10,CAR_CAP_MRC!$A:$L,6,0),"")))))
I have pulled the individual VLOOKUPS out separately to test them and they are all working perfectly with this part of the formula VLOOKUP(D10,CAR_CAP_MRC!$A:$L,4,0) resulting in the correct output but when I put them all together, I get no result.
Hi! Unfortunately, I can't verify your formula as I don't have your data. Note that you use $A:$K in the first VLOOKUP and then $A:$L after that.
Hi,
I want pull data from Multiple Tabs using formula .
Example.
TAB1 - Name - Address - Phone number
Hari - BNG - 66666
TAB2 - Name - Address - Phone number
Anand-BNG-7777
This data wants to sit in one sheet please help
Hi! If you want to merge data from two worksheets, check out these instructions: Merge multiple sheets into one.
i have two sheets where if the heading of a column matches with the value in another sheet q column then vlook up is executed. if it is false the it will jump to next heading this sequence goes on for 8 times .
but in third row though it is matching with the heading the we lookup formula is identifing a273 as 103642 where as it is 066-50007-1531
=IFERROR(IF(ac_matched!$B$1=removals_only!Q273,VLOOKUP(removals_only!A273,ac_matched!$J:$J,1,0),IF(ac_matched!$C$1=removals_only!Q273,VLOOKUP(removals_only!A273,ac_matched!$K:$K,1,0),IF(ac_matched!$D$1=removals_only!Q273,VLOOKUP(ac_matched!A273,ac_matched!$L:$L,1,0),IF(ac_matched!$E$1=removals_only!Q273,VLOOKUP(removals_only!A273,ac_matched!$M:$M,1,0),IF(ac_matched!$F$1=removals_only!Q273,VLOOKUP(removals_only!A273,ac_matched!$N:$N,1,0),IF(ac_matched!$G$1=removals_only!Q273,VLOOKUP(removals_only!A273,ac_matched!$O:$O,1,0),IF(ac_matched!$H$1=removals_only!Q273,VLOOKUP(removals_only!A273,ac_matched!$P:$P,1,0),IF(ac_matched!$I$1=removals_only!Q273,VLOOKUP(removals_only!A273,ac_matched!$Q:$Q,1,0),"")))))))),"nhpn")
Hi -
Thank you for your valuable help.
This question is closely, though not perfectly, aligned with this topic.
I frequently have the pattern
=if( iserror (vlookup(...),"error", vlookup(...))
where the arguments to the two vlookups are identical. And where it's very easy to get the two argument lists out of sync.
If there a way to avoid the duplication?
To help illustrate... in programming languages I could do something like
x = vlookup(...)
if iserror(x) return "error"
else return x
Thanks,
Mike
Hi
Thanks a lot for your help, i want a formula based on the following thanks
=IFERROR(VLOOKUP(C452,sheet1!$C$1:$S$100,7,FALSE),
IFERROR(VLOOKUP(C452,sheet2!$C$1:$R$100,7,FALSE)))
if 7 COLUMN has two values first 0 and second above then 0
i want vlookup to skip the 0 and get the second value
7 = 0
7 = 1
Hello!
If you want to search with IF conditions, use this guide: IF VLOOKUP in Excel. I hope my advice will help you solve your task.
Hi!
I had a problem with my IFERROR VLOOKUP formula. It does work if I input a value on the search key but when I used IMPORTRANGE formula on the search key, the IFERROR VLOOKUP formula gives a blank cell.
Can you help me with this.
Thank you.
I have a following fx that looks up data on two tables to return a value depending on the audience group. This is the current fx that returns the correct results: =IF(VLOOKUP('Master Role-Course Data'!$A$1,'Master Role-Course Data'!$A$3:$AB$43,H9)=0,"",VLOOKUP('Master Role-Course Data'!$A$1,'Master Role-Course Data'!$A$3:$AB$43,H9)).
However, I want to add an 'if error' to add text prompting the user to perform an action to select a business group in another cell otherwise the return cell will show the standard "N/A" value.
Hi!
Please re-check the article above since it covers your case.
Hello, great info!
I was wondering what a formula would look like to display the difference between 2 cells, not just "match" or "not match", example below.
cell A1 contains the following text - pie,cake,donut.
cell B1 contains the following text - pie,cake,donut,taco.
in cell C1 we would like to see output = taco
Thanks in advance!
Hi!
You might consider using the Fuzzy Duplicate Finder. It is available as a part of our Ultimate Suite for Excel that you can install in a trial mode and check how it works for free.
Is there a way to add another criteria to this formula? I want to add if this range has a certain word, in my case OUTSTANDING, to return BLANK"". Current Formula: IFERROR(VLOOKUP(B9,'REPORT!A:C,3,FALSE),"")
Hello!
Based on your description, it is hard to completely understand your task. However, I’ll try to guess and offer you the following formula with IF statement:
=IF(SUM(--(A:C="OUTSTANDING")),"",IFERROR(VLOOKUP(B9,A:C,3,FALSE),""))
Hi , just want to ask how to called one raw one data and make exceptional towards data not to call in and remarks as NA . For example , i got Malaysia and London in one row of data and try to called out for malaysia only and make the data of london come out as NA
Hi!
I am not sure I fully understand what you mean. Here is the article that may be helpful to you: Fixing #N/A error in VLOOKUP. If this is not what you wanted, please describe the problem in more detail.
Hello, can you kindly help me understand what we can use this formula for?
=IFERROR(VLOOKUP(A17381,A:AA,25,FALSE)-VLOOKUP(IF(J17381=1,I17381)&0&(((MONTH(DATE(I17381,J17381,1)-1)))&0)&(K17381&0)&L17381,A:AA,25,FALSE),0)
Hi!
It is very difficult to understand a formula that contains unique references to your workbook worksheets. What is the problem you want to solve?
I am trying to do a computation where Jan will be subtracted from Feb, March from Feb without having to manually change the months
=IFERROR(VLOOKUP(A17381,A:AA,25,FALSE)-VLOOKUP(IF(J17381=1,I17381)&0&(((MONTH(DATE(I17381,J17381,1)-1)))&0)&(K17381&0)&L17381,A:AA,25,FALSE),0)
=IFERROR(VLOOKUP(A17381,A:AA,25,FALSE)-VLOOKUP((I17381&0)&(((MONTH(DATE(I17381,J17381,1)-1)))&0)&(K17381&0)&L17381,A:AA,25,FALSE),0)
The second formula worked all the months from 2018 to 2021 but its not able to perform same subtraction in 2022 from Jan to Feb
Hi!
The information you provided is not enough to understand your case and give you any advice, sorry. What does the phrase mean "Jan will be subtracted from Feb"? Could you please describe it in more detail? What result do you want to get? Give an example of the source data and the expected result.
Hi,
I came across your solution to a poster's problem posted in the comments on Nov 30th.
=IF(IFERROR(VLOOKUP(E2,A1:B9,2,FALSE),"")="","", IFERROR(VLOOKUP(E2,A1:B9,2,FALSE),""))
This to me seems to be a neat solution to dealing with the problem when VLOOKUP returns #N/A or 0.
I'm struggling to understand how it works. As I read it it works as follows but it doesn't make sense
If VLOOKUP outputs an error, write "" as output and if output is "" then write "".
If vlookup does not output an error (i.e. a result or 0) then run vlookup again.
So the first IfError traps #N/A as "" but I don't see how the second IfError differentiates between a result and 0.
Can someone explain please.
Thank you.
Loved the simplicity
HI I WANT TO FIND OUT ONLY ONE PARTICULAR PRODUCT OUT OF THREE PRODUCTS BY MATCHING
FOR EXAMPLE: THERE ARE DIFFERENT VALUES FOR KIRAN OUTLET LIKE BISCUIT-CHOCALATE-DRINK AND OUT OF THEM IF I WANT TO IDENTIFY ONLY BISCUIT HOW TO FIND OUT
Hi!
To find subtext within text, you can use the formula
=ISNUMBER(SEARCH("BISCUIT",A1,1))
If this is not what you wanted, please describe the problem in more detail.
hi
kiran enterprises has three different products,
for example I want to match product wise and give value
result should be like this - outlet name - biscuits - chocalate - drink
outlet name biscuits drink chocalate
kiran 25 35 65
Hi, this works! I learnt a lot. Thanks.
I am not a native English speaker, but I think your example and words are easy to understand.
Thanks again.
Alexander who are you, man ??......I really don't have words to thank you.
My requirement was too much complex and too much ifs and what ifs and only you did it.( I contacted other guys to make this formula)
Now this formula is perfect.
Last but not the least... so much apologies for wasting your time and effort by telling the info in bits and pieces ( I keep on smiling while thinking about this) And loads of thanks and good wishes for you.
how to make a formula for first looking up a value in one sheet(sheet A) and if not found then look that value in another sheet(sheet B) and after finding that value in sheet B, returns the message rather than returns the sheet B value.
Hi,
I think the formula might look something like this:
=IFERROR(VLOOKUP(B2,'Lookup table'!$A$2:$B$5, 2, FALSE), IF(ISERROR(VLOOKUP(B2,'Lookup table1'!$A$2:$B$5, 2, FALSE)),"Not found","Found"))
Here is the article that may be helpful to you: VLOOKUP with IF statement in Excel.
OooH...Love u Alexander. The formula worked.
IS there anything I can do for u?
Hi!
Thank you for your positive feedback! It’s the best incentive for us to keep up and improve :)
Hi!
Hope u will be in good health.
Can I let myself clear on the syntax of the following formula u made for me.
=IFERROR(VLOOKUP(B2,'Lookup table'!$A$2:$B$5, 2, FALSE), IF(ISERROR(VLOOKUP(B2,'Lookup table1'!$A$2:$B$5, 2, FALSE)),"Not found","Found"))
Some of my understanding: If I start from inside out;
ISERROR Syntax; Looking up B2 in table1,if an error(e.g#NA) returns then it is true, else false.
IF syntax ; if ISERROR returns 'true'(i.e an error), it would write 'Not Found' and if ISERROR returns false (i.e no error) then it would return text 'Found' .
Here 'ISERROR' is logical text of 'IF' function.
There must be some error (i.e #NA) to be traped by ISERROR in order to return the text "Not Found" or ''Found''.
But if I am correct ( definitely not), the result of formula is inconsistent with the above understanding.
Your formula works like: if the value (B2) is in 'Lookup table', it returns that value and if it does not find B2,it goes to 'Lookup table1' and if B2 is found ,it provides text 'Found' and if it is not found in 'Lookup table1' either then it says 'Not Found'.
Hello!
I wrote this formula based on the description you provided in your original comment. You didn't say anything about what to do if a value is found in table A. Therefore, a value is returned.
I didn't quite understand what you want. Perhaps a lookup in the first table should also return a message.
=IF(ISERROR(VLOOKUP(B2,A!$A$2:$B$5, 2, FALSE)), IF(ISERROR(VLOOKUP(B2,B!$A$2:$B$5, 2, FALSE)),"Not found","Found"),"Found")
Ok Alexander I make myself simple.
First, I want to understand the syntax of the formula.
Second, your formula works very well. It returns the value of Table A and if not found here, it finds the value in Table B and if it finds the value in Table B, returns the message instead of the value. If the value is not found in both the tables, it returns ''Not Found but if there is NILL value, it also returns Not found.
I want that this formula would return nothing if I give nothing in the cell.
Hi,
Can you fully describe your problem? I waste a lot of time redoing your formula.
"if I give nothing in the cell" — what cell are you talking about now? About B2? About $A$2:$B$5? Or all of them?
Apologies for not being clear Alexander and so much apologies for wasting your precious time.
If I let B2 empty, the formula would return nothing.
=IF(ISERROR(VLOOKUP(B2,A!$A$2:$B$5, 2, FALSE)), IF(ISERROR(VLOOKUP(B2,B!$A$2:$B$5, 2, FALSE)),"Not found","Found"),"Found")
Hello!
The formula below will do the trick for you:
=IF(ISERROR(VLOOKUP(B2,A!$A$2:$B$5, 2, FALSE)), IF(ISERROR(VLOOKUP(B2,B!$A$2:$B$5, 2, FALSE)), IF(B2="","","Not found"),"Found"),"Found")
Hope this is what you need.
Oh great great Alexander. It worked.
Your response time is so amazing. I am really unable to understand how you manage to do so. You have helped me so much in my job.
Hi,
Alexander one thing was missing.
The value of A!$A$2:$B$5 is not able to return. Instead the text string (Found) has returned.
Apologies. May I send you the data if you are comfortable with?
Hi,
Try the following formula:
=IF(NOT(ISERROR(VLOOKUP(B2,A!$A$2:$B$5, 2, FALSE))), VLOOKUP(B2,A!$A$2:$B$5, 2, FALSE), IF(ISERROR(VLOOKUP(B2,B!$A$2:$B$5, 2, FALSE)),"Not found",IF(B2<>"","Found","")))
Hope you haven't forgotten anything else.