Excel: Use VLOOKUP to Return Yes or No

---

You can use the following VLOOKUP formula in Excel to attempt to look up a specific value in a range and return either “Yes” or “No” to indicate if the specific value exists in the range or not:

=IF(ISNA(VLOOKUP(D2,$A$2:$A$11,1,FALSE)), "No", "Yes")

This particular formula attempts to look up the specific value in cell D2 in the range A2:A11 and returns either “Yes” or “No” to indicate whether the specific value exists in the lookup range or not.

The following example shows how to use this formula in practice.

Example: Use VLOOKUP to Return Yes or No in Excel

Suppose we have a list of teams in column A in Excel and another list of someone’s favorite teams in column D:

Suppose we would like to check if each team in the Favorite Teams list exists in the larger list of teams or not.

To do so, we can type the following formula into cell E2:

=IF(ISNA(VLOOKUP(D2,$A$2:$A$11,1,FALSE)), "No", "Yes")

We can then click and drag this formula down to each remaining cell in column E:

Column E returns either “Yes” or “No” to indicate whether each team in the Favorite Team column exists or not in column A.

For example:

• Thunder exists in the Team column, so the VLOOKUP formula returns Yes.
• Cavs does not exist in the Team column, so the VLOOKUP formula returns No.

And so on.

How This Formula Works

=IF(ISNA(VLOOKUP(D2,$A$2:$A$11,1,FALSE)), "No", "Yes")

Here is how this formula works:

First, we use VLOOKUP to attempt to look up the value in cell D2 (“Thunder”) in the range A2:A11.

This will either return Thunder or #N/A.

Then we use the ISNA function to check if the VLOOKUP returned #N/A or not.

This will either return TRUE or FALSE.

Lastly, we use the IF function to return “No” if the ISNA function evaluates to TRUE or return “Yes” if the ISNA function evaluates to FALSE.