Excel tutorials

Sometimes we want to compare columns and find out if a value is present or missing in the other column.

In this example we have our 2 columns with fruits – we want to determine if the specific fruit in List A is present or missing in List B.

Below is my example data:

A quick look gives us that the first 3 are present in both lists, and also the last element is present in both lists. Orange for example, does not occur in LIST 2, so column C shows #N/A for Orange.

We are going to use the the VLOOKUP() function to reach this result.

In Cell C2 – type the following formula:

=VLOOKUP(A2;$B$2:$B$7;1;0)

The above formula check each name in LIST 1 against the elements in LIST 2. The first parameter is the lookup-value, that is for the first row A2 (Banana) – then we check if Banana is present in the range B2:B7. We search in the B column – that is the 3rd parameter. Finally we want an exact match – the last parameter.

It looks better if we output some text instead of returning N/A or the value itself. So we can wrap the above formula in an IF-ISERROR clause.