Tutorial: How to use VLOOKUP to test if a value exists in another list

By | September 5, 2015

The challenge

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.

vlookup testing for a value in a list

 

The Solution

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:

vlookup test whether value exists

 

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:

excel vlookup for value in a list true or false

 

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.

Leave a Reply

Your email address will not be published. Required fields are marked *