Excel – using VLOOKUP to remove rows based on matching data from another column

Posted by Shek on December 10, 2012 under Misc | Be the First to Comment | Total View: 7,387 views

In this post, I will demonstrate how to remove rows which are not matching the records in the other column of Excel with VLOOKUP. Although I am using Excel 2010 for the demonstration but this method should work with Excel 2003 as well.


First we have some excel data at column A and B which are the “ID” and “email” respectively:

Excel Vlookup

Then we include the ID which we want to keep only at column C:

Excel Vlookup

The row we want to keep is with ID “3,10,17,19″ at column A.

We will begin with the matching operation here:

At column D, we input the formula to create all un-matched with “remove” in it:

1
=IF(ISNA(VLOOKUP(A2:A20,C$2:C$5,1,FALSE)), "remove", VLOOKUP(A2:A20,C$2:C$5,1,FALSE))

The formula basically means:
we will look up from A2 to A20 if there is any matched from C2 to C5 we will fill the cell with “remove” (those cell we are going to remove).

Drag the formula from D2 to D20 so that the formula will fill up the series for all the rows in column D (notice that in the formula I put $ sign in the C$2:C$5 of which they will not change even the formula is being drag down):

we get:

excel vlookup

Now we want to remove the unmatched rows with “remove” cells at column D: