VLOOKUP is a built-in Excel function. If you give a specific value in the VLOOKUP function it finds the value in a column and returns the corresponding value in another column. But, if the value is not found, it gives an error message (#N/A) which is sometimes not expected in a datasheet. This article mainly focuses on how you can omit the error message with the help of the ISERROR function and the VLOOKUP function in Excel. So, let’s get started.
Download Practice Workbook
You may download the following Excel workbook for better understanding and practice it by yourself.
3 Useful Ways to Use ISERROR and VLOOKUP Functions in Excel
Sometimes, It is not very professional to show error messages in your datasheet. To create a polished workbook you must omit the error messages by applying different functions. Here, we have merged IF, ISERROR and VLOOKUP functions so that while using VLOOKUP function we can prevent having the error message. And most importantly we have shown how to replace the error with a custom text, blank cell, or Yes/No text which may work great in some real situations.
Method 1: Combining ISERROR, IF, and VLOOKUP Functions in Excel to Replace Errors with Custom Text
Suppose, We have a table that shows the name of the students and their corresponding Courses taken in a semester. We want to know each student’s corresponding course in another column without having any error message. Instead of an error message we want a custom text “Not Registered Yet”.
Following are the steps.
Steps:
- Write the formula in cell C5.
- To see the result, press ENTER.
- Therefore, we will get the Course Taken by Peter which is Fluid Mechanics.
- Lastly, we will copy the formula down to cell C13 by using the AutoFill feature.
- Consecutively, we can see that it returns our custom text where any student has not taken any course yet.
Formula Breakdown
- VLOOKUP(B5,$E$5:$F$9,2,FALSE), firstly, this portion looks up for (lookup value=B5) in (table array=E5:F9) and (column index=2). FALSE indicates the exact match of cell value B5.
- ISERROR(VLOOKUP(B5,$E$5:$F$9,2,FALSE)),”Not Registered Yet”,VLOOKUP(B5,$E$5:$F$9,2,FALSE), afterwards,the ISERROR function gives the (value=Not Registered Yet”) for error message.
- IF(ISERROR(VLOOKUP(B5,$E$5:$F$9,2,FALSE)),”Not Registered Yet”,VLOOKUP (B5,$E$5:$F$9,2,FALSE)), lastly, the IF function does the logical test, (value if true=”Not Registered Yet”) and (value if false =”lookup value”).
Read More: How to Use IF with ISNA Function in Excel (3 Ideal Examples)
Similar Readings
- How to Use ISNA Function in Excel (3 Suitable Examples)
- Use ISEVEN Function in Excel (2 Suitable Examples)
- How to Use ISODD Function in Excel (4 Suitable Examples)
- Use ISNUMBER Function in Excel (7 Examples)
- How to Use ISBLANK Function in Excel (3 Examples)
Method 2: Merging ISERROR, IF, and VLOOKUP Functions to Return Blank Cell for Errors in Excel
In case, you want to keep the error message cells blank, go after the steps below.
Steps:
- Firstly, Put down the formula in cell C5.
- Then, click the ENTER.
- Accordingly, we will get the output.
- Lastly, imitate the formula down through cell C13 by implementing the AutoFill feature.
- As a result, we will get the cells blank where error messages would appear.
Formula Breakdown
- VLOOKUP(B5,$E$5:$F$9,2,FALSE), in the first place, this portion looks up for (lookup value=B5) in (table array=E5:F9) and (column index=2). FALSE indicates the exact match of cell value B5.
- ISERROR(VLOOKUP(B5,$E$5:$F$9,2,FALSE)),” “,VLOOKUP(B5,$E$5:$F$9,2,FALSE), secondly, the ISERROR function delivered the (value=” “) which is a blank cell for #N/A.
- IF(ISERROR(VLOOKUP(B5,$E$5:$F$9,2,FALSE)),” “,VLOOKUP (B5,$E$5:$F$9,2,FALSE)), eventually, IF does the logical test, (value if true=” ”) and (value if false =”lookup value”).
Read More: Nested IF and ISERROR Formula in Excel (2 Practical Examples)
Method 3: Nesting ISERROR, IF, and VLOOKUP Functions to Get Yes/No Value
Wherever you need the output of the blank cells would show “No”, and the cells which carry value would show “Yes”, follow the instructions below.
Steps:
- In the first place, copy this formula in cell C5.
- Next, hit ENTER.
- As a result, we will get the value “Yes” as Peter has taken a course.
- Eventually, duplicate the formula down to cell C13. As a result, we got the Yes/No text in the cells.
Formula Breakdown
- VLOOKUP(B5,$E$5:$F$9,2,FALSE), So, this part of the formula looks up for (lookup value=B5) in (table array=E5:F9) and (column index=2).
- IF(ISERROR(VLOOKUP(B5,$E$5:$F$9,2,FALSE)),” “,VLOOKUP(B5,$E$5:$F$9,2,FALSE)), In conclusion, the IF function does the logical test (value if true=”No ”) and (value if false =”Yes”).
Read More: How to Use SUMIF and ISBLANK to Sum for Blank Cells in Excel
Conclusion
This article shows 3 ways in which the IF, ISERROR, and VLOOKUP functions can be nested to get desired results. Hope this article will help to omit the error message while using the VLOOKUP function. Different situations may occur where these formulas need to be modified. If you face any problems regarding the formulas, please leave a comment. We will try to give the best possible solution.
Related Articles
- How to Use COUNTIF & ISNUMBER to Count Numbers in Excel
- [Fixed!] ISNUMBER Is Not Working in Excel
- How to Use NOT and ISNA Functions in Excel (2 Examples)
- Use ISNA and MATCH Functions in Excel (2 Useful Examples)
- How to Use ISBLANK Function to Check If Cell Is Blank in Excel
- Use ISLOGICAL Function in Excel (4 Examples)
- How to Use ISTEXT Function in Excel (8 Suitable Examples)