Table of Contents
VLOOKUP with IF Statements in Google Sheets can be used to compare two ranges of data and return a certain value depending on the result of the comparison. This can be done by combining the VLOOKUP function with an IF statement. The VLOOKUP function checks for a certain value in a range of data and the IF statement checks to see if the VLOOKUP result matches a certain criteria. If the criteria is met, the IF statement will return a specified value. If the criteria is not met, the IF statement can be set to return another value.
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))
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.