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: 10,094 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:

  • Copy and paste column D to column E with paste special – only paste the value, now we have column E identical to column D but column E is with the actual values.
  • Now we have column E with “remove” cells, highlight column E then go to “find & select”, then “replace”, replace text “remove” with nothing (blank), so all the cells in column E with “remove” become blank.
  • After, highlight the whole column of column E and press “F5″, then click on “special” and choose “blank”, then click “OK”.
  • excel vlookup

    excel vlookup

    As you can see, all the BLANK cell in column E are now turn into blue color and ready for the removal.

    With all the intended cells to be removed in blue color, don’t do anything except click on the “Delete” button under “Home” tag and click on “Delete Sheet rows”, and all the un-match cells will be removed.
    excel vlookup

    Not what you want? Try a Search

    Add A Comment

    *