Delete unwanted rows using Vlookup in MS Excel

---

​In MS Excel, the Vlookup function could be used in order to delete unwanted records. Here’s how:
Consider the two columns below.

​Let’s say, we want to suppress all records with exception of few which have certain User IDs. I.e. User ID= 1, 5, 10, and 15. We list these values in our spreadsheet.

To achieve this, I will Insert a Vlookup statement that will return null values whenever the User ID does not fall within the list of ID’s that we want to retain.

=IF(ISNA(VLOOKUP(B8,G$2:G$5,1,FALSE)), "Delete", VLOOKUP(B8,G$2:G$5,1,FALSE))

The statement above returns the users IDs that match the list of four and whenever there is no match the value “Delete” will be returned.

​By dragging this formula down we will get a blank cell for every time the User ID is not within the retention list (i.e. in column G). 

​The next step will involve copying all the contents of column E and pasting as special (Value) in column F.

​Next we need to replace the Delete values in column F with blanks values. See the illustration below:

​The last step is to delete all blank cells in column F, which will in turn delete the entire row whenever we have a blank cell. In one of my previous blog posts, I described a quick method for deleting blank rows in MS Excel. Please click here to review it. Here is the final outcome after all blank cells are omitted. 

---