LOOKUP to return default value if not found match value in Excel
In Excel, it will return the error value #N/A if it does not find the matched value while using VLOOKUP function as below screenshot shown. For avoiding the error value appears, you can use a default value to replace the error value if not found the matched one.
Combine IFERRROR function and VLOOKUP function to return default value
Please select a cell to place the formula, type this formula:
G2 is the lookup value, B2:D7 is the table array, 3 is the number index you want to return, Not Found is the default value that will display if the VLOOKUP function does not find out matched value.
Press Enter key to return the match, if there is no match, it will display Not Found.
Note: if you are using Excel 2007 or prior versions, you can try this formula =IF(ISERROR(VLOOKUP(G2,$B$2:$D$7,3,0)),"Not Found",VLOOKUP(G2,$B$2:$D$7,3,0))
Tip: If you just want to replace the error values to a specific text after applying the general VLOOKUP formula =VLOOKUP(F2,$B$2:$D$7,3,0), you can use the Error Condition Wizard of Kutools for Excel to quickly replace errors to zero, blank cell or a text as you need. Click for free download it now.
LOOKUP and return default value by using a handy tool
If you have Kutools for Excel, you can apply this LOOKUP functions group to solve this job in different cases. Supposing in this article's case, you can apply the LOOKUP from Right to Left utility to handle it.
1. Click Kutools > Super LOOKUP > LOOKUP from Right to Left.
2. In the LOOKUP from Right to Left dialog, do as below steps: 1) In the Lookup values and Output Range section, choose the lookup value and output cell; 2) Check Replace #N/A error value with a specified value checkbox, and enter the text you want to return while returning error; 3) In the Data range section, choose the data range including or excluding headers,then specify the key column (lookup column) and return column.
3. Click OK, then the value will be lookuped or return a value you specify.