In the following example we want to test for a value in a list but we just want to check that it’s there, not return any corresponding value. This is a good way to find out how many values in one list appear in another.
Using our standard construction of a VLOOKUP formula:
=VLOOKUP ( SearchFor , WhereToSearch , WhichColumn , NearOrExact )
- The SearchFor value will be cell C1
- WhereToSearch will simply be column A (denoted “A:A”)
- WhichColumn will be column 1
- NearOrExact will be exact (so “False”)
So if we just left it like that then if the value did appear in the list in Column A then it would return the same value. If it wasn’t there we’d get a #N/A result. So this is what we will include in our formula.
Here’s how. We use the isna function which tests whether a formula will return a #N/A result.
=if(isna(the formula we built above), “No”, “Yes”)
So in English this is saying, “if the VLOOKUP formula that we built above would return #N/A then return a No, otherwise return a Yes“.
Here’s what the formula looks like:
This will return a No in the first instance and to test that it works, we can add a value below cell c1 that we can see in the list and drag the VLOOKUP formula down one cell. See the result below:
Want to get good at using the VLOOKUP function?
Download our practical VLOOKUP tutorial workbook with step-by-step instructions and examples to help you become a vlookup pro.