Check if value exists in another column of a Microsoft Excel document

- 2 mins

Today I spent some time looking for this trick. The function to use is called VLOOKUP. From Office Support web page:

Use VLOOKUP, one of the lookup and reference functions, when you need to find things in a table or a range by row. For example, look up a price of an automotive part by the part number.

Function VLOOKUP requires 4 arguments. the first two are easy to understand, but the third

  1. The value to look for.
  2. The range where we want to look for the value.
  3. Index of the column in the range containing the return value.
  4. [optional] TRUE for approximate match and FALSE for a perfect match.

Let’s see an example for argument 3, from the same Office Support site:

For example, if you specify B2:D11 as the range, you should count B as the first column, C as the second, and so on.

So, this means that given a range of a single column (aka. B2:B2550) this argument will be 1. If the range includes more that one column, then it should take the index of the value to return.

The following is an example of using VLOOKUP to find the value of an item:

vlookup area

In my case I want to get a typical boolean TRUE / FALSE to know if the value to look for is in a given column or not. To this end I used two more functions: IF and ISERROR.

The following picture shows the use of VLOOKUP to check if the elements in column A are in column C using the 4Th argument of VLOOKUP, set to FALSE:

vlookup two columns

As can be see, when an element from A is not present in the C it returns #N/A (aka. an error). So we can use ISERROR to check if VLOOKUP raises an error and IF to return TRUE or FALSE:

vlookup true false

The final formula follows:

Carles Hernandez-Ferrer

Carles Hernandez-Ferrer

Bioinformatics, data analysis and software development

rss facebook twitter github gitlab youtube mail spotify lastfm instagram linkedin google google-plus pinterest medium vimeo stackoverflow reddit quora quora