In Vlookup or Hlookup you can avoid showing all those error messages like N/A, VALUE or REF errors. Instead, you can club with other formulas to return zero or a custom message if the error occurs. You can do this by wrapping your VLOOKUP formula in the IFERROR function from Excel 2007 or with IF / ISERROR in earlier Excel versions.
Syntax: IFERROR(value, value_if_error) Here in the value part of the formula, you can put the Vlookup formula and the value_if_error part you type the value you want if there is an error. For example, in this formula =IFERROR(VLOOKUP($H$2,$B$2:$F$10,2,FALSE),"") you will get a blank cell if the lookup value is not there. For that, you can give Vlookup as the first parameter in Iferrror function and second parameter as double quotes to get a blank cell. Here is another formula where you will get zero in the cell if the lookup value is not there =IFERROR(VLOOKUP($H$2,$B$2:$F$10,2,FALSE),0). We have specified 0 as the second parameter of the Iferror function to get 0. Or else you can give a custom text Not found as the second parameter as in this formula =IFERROR(VLOOKUP($H$2,$B$2:$F$10,2,FALSE),"Not found”).
Example: Open the file Chapter 5.1 - Vlookup MultipleSheets.xlsx Here we have used the combination of IFERROR and Vlookup to fetch data from multiple sheets. We have five sheets South, North, East, West and Report. See the image below to understand how the data is arranged. [example] Now in the Report sheet enter the formula given below in B3 cell. I have pasted this big formula into separate lines for readability as well as for you to understand. =IFERROR(VLOOKUP(B2,South!A1:B3,2,0), IFERROR(VLOOKUP(B2, North!A1:B3,2,0), IFERROR(VLOOKUP(B2,East!A1:B3,2,0), IFERROR(VLOOKUP(B2,West!A1:B3,2,0), "No Match Found")))) Since there are four sheets, we have to write the combination of IFERROR and Vlookup four times. The formula seems to be very lengthy, but the same formula is repeated four times with different sheet names. We started the formula with IFERROR followed by the Vlookup formula to fetch data from South sheet. Then it is followed by next IFERROR and the Vlookup function till all the sheets are covered. In the last parameter of the IFERROR, we have specified the text No Match Found to get if there is no match. This formula will first search for the name Sam in sheet South and if it is an error, it will search in next sheet North and then East and then West. If all these sheets don't have the name Sam then will give the message No Match Found.
IFERROR function was introduced in Excel 2007. So in prior Excel versions, you have to use the combination of IF and ISERROR functions to get the working version of IFERROR. So in other words, IF / ISERROR / VLOOKUP formula will give you IFERROR / VLOOKUP in previous versions. The general format of the formula will be like this. =IF(ISERROR(VLOOKUP formula), "Error message here ", VLOOKUP formula) An actual formula will look like this. =IF(ISERROR(VLOOKUP($H$2,$B$2:$F$10,2,FALSE)), "" VLOOKUP($H$2,$B$2:$F$10,2,FALSE)) You will get blank cell here as we have given double quotes in the middle. And if you want message Not Found you can rewrite the formula like this. =IF(ISERROR(VLOOKUP($H$2,$B$2:$F$10,2,FALSE)), "Not Found", VLOOKUP($H$2,$B$2:$F$10,2,FALSE)) And if you want a zero you can write the formula like this =IF(ISERROR(VLOOKUP($H$2,$B$2:$F$10,2,FALSE)), 0, VLOOKUP($H$2,$B$2:$F$10,2,FALSE))
Sometimes you find a match for the Vlookup data, but the corresponding cell will be empty. If the cell is empty, you should get a blank cell as return, but Vlookup will return zero for blank cells. [example] Here in this image, we are searching the score of John and we have not entered any score for John, means it is a blank cell. But if you do the Vlookup you will get zero as return. If you don't want a zero and instead want a blank cell, you can nest with IF and ISBLANK function. The format for this will look like this. =IF(ISBLANK(VLOOKUP formula),"",(VLOOKUP formula)) And the actual formula will look like this. =IF(ISBLANK(VLOOKUP(B5,A2:B3,2,0)),"",(VLOOKUP(B5,A2:B3,2,0)))
Vlookup will return N/A error if the value is not there. So this function will check whether the value returned is N/A or not. If it is N/A it will return true and if it is not N/A it will return false. Syntax: = ISNA ( value ) Now if you can club this function with IF function you can display any text you want. So final formula will be like this. = IF (ISNA ( Vlookup formula ), value_if_error, Vlookup formula ) In the Vlookup formula part, you have to type the formula you want. This formula will be same in two places where I have written the Vlookup formula. And in the value_if_error part, you can write a text like No Match Found in double quotes or whatever text you want. Also, you should keep in mind ISNA function will only check for N/A error, if any other errors are there, then that error will get displayed.
In Excel 2013 a new function is introduced called IFNA, combining IF and ISNA. So if you are using Excel 2013 or above you can simply use the formula directly.
So when you write a formula it will be like this IFNA(VLOOKUP(B1,Data,3,FALSE),"Not found")