Archive for July 18th, 2013

Excel Tip: Compare two lists (Method 2) July 18th, 2013

Vinod Kumar

This is in continuation to my previous post. Just to recap the problem statement – we want to find from our “List B” which is not in “List A”. The initial dataset can be found below first.

Method 1: Using MATCH() function.

Method 2: Using VLOOKUP() function.

Method 3: Using PivotTable.

As mentioned, in this blog post we will use VLOOKUP() function instead of MATCH() function. Now in F3 cell, let us start typing “=VLOOKUP(“ and press CTRL+A. This will bring the function arguments dialog.

Lookup_value: D3

Table_array: $A$3:$A$10 (this is the range to make comparison, remember F4 key after selecting the range, it will automatically lock the range using the $ synbols)

Col_index_num: 1 (the column ordinal based on range, we have just one column and hence it is 1)

Range_Lookup: FALSE (for exact match)

Press Enter. Now if a match is found you will see the value of column repeated. Next is to find the cross arrow from right corner cell and double click.

Now whereever we get an “#N/A” Error, it means we couldn’t find a match from other list.

We saw in our previous post how to get rid of the Error. We will use the “=IFERROR” function to add an value “Missing” and we will double click our right cross arrow.

Voila, now that is the second method of finding missing values in comparison to another range.

Continue reading...