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.

Tags: , , , , , , , , , ,

This entry was posted on Thursday, July 18th, 2013 at 08:00 and is filed under Technology. You can follow any responses to this entry through the RSS 2.0 feed. You can leave a response, or trackback from your own site.

5 Responses to “Excel Tip: Compare two lists (Method 2)”

  1. Beena Sharma says:

    That’s great learning.

    How about if the data values are in different folder/file? Please share example.

  2. Kiran says:

    Helpful article

  3. avinash reddy says:

    glad to learn one more tip from you thank u so much sir

Leave a Reply