Comments on: IFERROR with VLOOKUP in Excel - elegant way to trap errors

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 formula logic. Continue reading →

Comments page 2. Total comments: 89

  1. 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.

  2. Loved the simplicity

  3. 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

    1. 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.

  4. 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

  5. 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.

  6. 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.

  7. 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.

    1. 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.

      1. OooH...Love u Alexander. The formula worked.
        IS there anything I can do for u?

          1. 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'.

            1. 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")

              1. 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?

              2. 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.

              3. 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.

              4. 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")

              5. 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.

              6. 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.

              7. 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?

  8. Hi

    I am hoping that you can help me... I am struggling a lot with creating a spreadsheet of data on students marks and obtaining their grade depending on what level test they completed. So...:

    Tab 1 - student data with marks:
    Mark column 1, standard/ higher test, grade (what I am trying to find out)

    Tab 2 - marks and grades
    Table 1 - standard test
    Mark column 1, grade column 2

    Table 2 - higher test
    Mark column 1, grade column 2

    I require the mark to be obtained from tab 1, along with the column stating either standard or higher test - then obtaining the correct grade from tab 2.

    I am hoping you can help me as I have searched everywhere and although I have got an IFERROR function and VLOOKUP I cannot determine how to look up 2 values (mark and test type) and then look at 2 different tables to determine which grade is correct.

    So far I have this:

    =IFERROR(LOOKUP(D4,'CP7-8'!$A$3:$B$37),0)

    But that is just for looking up 1 value and 1 table???

    Please please help... confused teacher!

    1. Hello!
      I’m sorry but your task is not entirely clear to me. Unfortunately, without seeing your data it is difficult to give you any advice. Please provide me with an example of the source data and the expected result. For me to be able to help you better, please describe your task in more detail.
      Please check out the following article on our blog, it’ll be sure to help you with your task: Vlookup multiple matches in Excel with one or more criteria

  9. I am trying to make a formula to check multiple workbooks, I want the formula to check if an item is in column A e.g an egg then check to see if there is an amount in column C e.g 3, if not move on to another workbook to check the same columns. If there is an amount over 1, I want it to say yes and if it is 0 to say no.

    1. Hello!
      Sorry, I do not fully understand the task. If you only use one workbook then the formula is -

      =IF(AND(A1="egg",C1>3),"Yes","No")

      If this is not what you need, please explain your problem in more detail. Give an example of the source data and the expected result.

  10. Hello,

    I am trying to do a vlookup function while returning value as below:

    1. if blank cell, it will return blank
    2. not found, it will return blank
    3. if there's a value, it will return the value.

    Currently my formula is like this -

    =IF(IFERROR(VLOOKUP(E2,A1:B9,2,FALSE),FALSE),(VLOOKUP(E2,A1:B9,2,FALSE)),"")

    This formula works only if the value is integer but I encounter an error if the value inside the cell is a string. It will return as this - #VALUE for a string value.

    Please help.

    1. Hello!
      If I understand your task correctly, the following formula should work for you:

      =IF(IFERROR(VLOOKUP(E2,A1:B9,2,FALSE),"")="","", IFERROR(VLOOKUP(E2,A1:B9,2,FALSE),""))

      Hope this is what you need.

  11. I am trying to combine two If statements with their own VLookup. I can get it to work if its just one, but not the other. I am using an IF statement to return the data based on the value of cell X, e.g. if cell x = ND, Vlookup to return the value, and if cell x = ED, Vlook up to return the value.

    =IF(I6="ND",VLOOKUP(J6,'Post Codes'!A2:B117,2,FALSE),"", IF(I6="ED",VLOOKUP(J6,'Post Codes'!A2:E117,5,False)""))

    Please help!

  12. Is there a way to get from which sheet was the value picked up, for example in this case its south, and if there are multiple can it display from what sheets, the value was picked up.

    "=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")))"

    1. Hello!
      To see how your formula is being executed, you can use the Formula - Evaluate Formula menu item.

  13. This is fantastic stuff, but I have an issue that I cannot seem to get around.. Can you pls pls pls help..

    =IFERROR(VLOOKUP($N3245,'GB30'!$A$2:$K$99999,3,FALSE),IFERROR(VLOOKUP($N3245,'GB03'!$A$2:$G$9999,4,FALSE),"DNR"))))))

    Looking in 2 sheets for a given value and returning the associated lookup value when it's there. This is working fine, but when a cell is blank I am getting a "Zero" in the result. But I want it to show as an "empty cell" or "". How can these be adapted to result in ""

    I have been stuck on this for days!.

    Thanks in advance
    Jason

    1. Hello!
      You can check the obtained values using the IF function:

      =IF(IFERROR(VLOOKUP($N3245,’GB30′!$A$2:$K$99999,3,FALSE),IFERROR(VLOOKUP($N3245,’GB03′!$A$2:$G$9999,4,FALSE),”DNR”))))))=0,"",IFERROR(VLOOKUP($N3245,’GB30′!$A$2:$K$99999,3,FALSE),IFERROR(VLOOKUP($N3245,’GB03′!$A$2:$G$9999,4,FALSE),”DNR”)))))))

      I hope it’ll be helpful.

  14. Having trouble with the correct IF and VLookup formula. I have 2 separate sheets; 1 with names and the second with names and email addresses. I need a formula that will check to see if the a name on the first sheet is on the second sheet, and if it is, bring over the email address to the first sheet, in a new cell. Any ideas?

  15. Mine worked Perfectly!!!!!!!
    Thank you so much for the explanation, very easy to follow. :)

  16. Hi!
    I am trying to have my formula look up a text value and if the text is not present then move to the next row under it. If the text is present I want it populate onto a new sheet, if it isn't present then I don't want it to appear at all and I don't want blank rows to populate. Please help. Thanks in advance!

    1. Hello Madi!
      What you refer in the first sentence can be done only with the help of VBA.
      What you mention further, unfortunately I haven't got what you mean.
      Please explain your problem in other words and I’ll try to help you if it is possible.

  17. =IFERROR(VLOOKUP(A2,'Balance'!A:C,2,FALSE),0) is not working when A2 more than A999 how to solve it

    1. Hello!
      I have checked the work of your VLOOKUP function in my own file and haven’t found any error. However, the formula I used looks as follows:

      =IFERROR(VLOOKUP(A2,[’Balance’]Sheet1!A:C,2,FALSE),0)

      May this be the problem? If you are still getting an error, please describe the problem in more detail so that I’ll be able to help you better.

  18. when we revise the VLOOKUP Function, what is replaced with COLUMN Function

  19. I'm using excel office 360. When I do the vlookup, am getting the return value of the "text formula I typed in". How to fix this?

  20. Hello Mam,
    Please send me a video of IFERROR formula with vlookup.

  21. Ablebits is the best excel adviser, appreciated your job

    Regard,

  22. you are my number one !!!

  23. Please send me main excel accounting formulas at above email

  24. Hi, mam
    I have a data in sheet first is name and second is dob i want to do if month is coming dob highlights automatically

  25. Column A2:A173 contains 1,2 or is blank
    column c contains a date or is blank

    I need a formula to do the following:
    If column a contains a 1 or 2 look at the same row in column C and if that contains ANY entry (will most likely be a date) count it.

  26. Your timing is excellent. Just started new job and they gave me 3 files with 1 million rows each. Of couse split amongst tabs. I was going crazy with all the vlookup and now you helped me save hours of work. Thank you thank you.

  27. Hi

    Is there a way i can get a cell to return a blank cell after a particular date? if its before a date then use zero. Im having trouble with so many commands.

Post a comment



Thank you for your comment!
When posting a question, please be very clear and concise. This will help us provide a quick and relevant solution to
your query. We cannot guarantee that we will answer every question, but we'll do our best :)