You can use the following syntax to use a VLOOKUP with an IF statement in Google Sheets:
=IF(ISNA(VLOOKUP(D2, A2:B11, 2, FALSE)), "", VLOOKUP(D2, A2:B11, 2, FALSE))
This particular formula looks up the value in cell D2 in the range A2:B11.
If the value exists, then this formula returns the corresponding value in column 2 of the range.
If the value does not exist, then a blank is returned.
Note: The FALSE argument tells Google Sheets to look for exact matches instead of approximate matches.
The following example shows how to use this syntax in practice.
Example: Use VLOOKUP with IF Statement in Google Sheets
Suppose we have the following dataset in Google Sheets that shows the points scored by basketball players on various teams:
Suppose we attempt to use the following VLOOKUP formula to look up the team name in cell D2 in column A and return the corresponding points value in column B:
=VLOOKUP(D2, A2:B11, 2, FALSE)
The following screenshot shows how to use this formula in practice:
The VLOOKUP formula returns #N/A since “Nuggets” does not exist in the team column.
To return a blank value instead of #N/A, we can use the following formula:
=IF(ISNA(VLOOKUP(D2, A2:B11, 2, FALSE)), "", VLOOKUP(D2, A2:B11, 2, FALSE))
The following screenshot shows how to use this formula in practice:
Notice that a blank value is returned this time instead of #N/A.
Also note that you could return a different value instead of a blank if you’d like.
For example, you could use the following formula to return “Team Not in Dataset” if the team name you use in the VLOOKUP formula is not found in the dataset:
=IF(ISNA(VLOOKUP(D2, A2:B11, 2, FALSE)), "Team Not in Dataset", VLOOKUP(D2, A2:B11, 2, FALSE))
The following screenshot shows how to use this formula in practice:
Notice that the formula returns “Team Not in Dataset” since the Nuggets don’t exist in the team column.
Additional Resources
The following tutorials explain how to perform other common tasks in Google Sheets:
Google Sheets: How to Use VLOOKUP From Another Workbook
Google Sheets: Use VLOOKUP to Return All Matches
Google Sheets: Use VLOOKUP with Multiple Criteria
