Table of Contents
IFERROR can be used with VLOOKUP in Google Sheets to create a formula that displays a custom result when a lookup value isn’t found. This allows users to provide appropriate feedback when a VLOOKUP doesn’t find the value it’s looking for. To do this, you combine the IFERROR function with the VLOOKUP function in the same cell. The IFERROR function takes two arguments, the value to test and the value to return if an error is detected. The VLOOKUP function then takes the value to be tested as its first argument and the rest of the VLOOKUP arguments as normal. This combination allows users to customize the response when VLOOKUP can’t find the value it’s looking for.
You can use the following formula with IFERROR and VLOOKUP in Google Sheets to return a value other than #N/A when the VLOOKUP function does not find a particular value in a range:
=IFERROR(VLOOKUP("string",A2:B11,2,FALSE),"Does Not Exist")
This particular formula looks for “string” in the range A2:B11 and attempts to return the corresponding value in the second column of this range.
If it does not find “string” then it simply returns “Does Not Exist” instead of a #N/A value.
The following example shows how to use this formula in practice.
Example: Use IFERROR with VLOOKUP in Google Sheets
Suppose we have the following dataset that shows the number of points scored by various basketball teams:
Suppose we use the following VLOOKUP formula to find the number of points associated with the “Raptors” team:
=VLOOKUP("Raptors",A2:B11,2,FALSE)
The following screenshot shows how to use this formula:
This formula returns a value of #N/A because the “Raptors” do not exist in the Team column.
However, we can use the following IFERROR function with the VLOOKUP function to return a value of “Does Not Exist” instead of #N/A:
=IFERROR(VLOOKUP("Raptors",A2:B11,2,FALSE),"Does Not Exist")
The following screenshot shows how to use this formula:
The following tutorials explain how to perform other common operations in Google Sheets: