Excel – using VLOOKUP to remove rows based on matching data from another column
First we have some excel data at column A and B which are the “ID” and “email” respectively:
Then we include the ID which we want to keep only at column C:
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:
=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):
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”.
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.