There are multiple methods of extracting duplicate records. But when it comes to VLOOKUP then this video will help you.
First, lets start with the Filter Function. Filter Function is only available in Office 365.
Type, Filter, it will ask for an array, that is the data range that you need to highlight. Next it will ask to include, so you need to highlight the Name column range and type an equal sign and point to the name. The filter function will extract all the records that match the name and give you the output of all the relevant columns.
Now for the Vlookup….before we use Vlookup, we need to use the Countif function to get us the number sequence. Once you get the sequence, concatenate the number with the name. Now, we can use the Vlookup function with the Row function. The row function in excel gives us the current row number.
So with Vlookup, I use the row function to get the sequence number and then concatenate it with the name. The second argument is the table array, but here you need to start from Column A and then select all the columns. For the column, we need to find another dynamic way to get the column numbers. Same way as we used the row function, I can use the Column function to get the column numbers. The last argument is False to get an exact match. Now when you drag the formula down, you will get #N/A errors, but we can use the IFNA function to get rid of the errors and show blanks if there are no more records.
Watch the video Step by Step to understand and practice it!!