How to find duplicate values in different Excel sheets and two columns in Excel
The Vlookup function can be used for a variety of lookups, and one of them is to filter for duplicate data, which can filter for duplicate data in two columns and find the same data for two tables.
Chapters:
0:00 How to find duplicate values in two columns in Excel?
0:29 Explanation of the formula for One
0:50 How to find duplicate values in different Excel sheets?
2:21 Explanation of the formula for Two
Description:
How to find duplicate values in different Excel sheets and two columns in Excel using Vlookup
When filtering two columns of duplicate data, not only one duplicate data is returned, but all duplicates are marked; When we find the same data in two tables, the two tables can be located in the same Excel document or in two Excel documents, and all duplicate data can also be marked. When we find the same data of two tables in different Excel documents, we need to write the document name and workbook name in the range found, so that Excel can find the range found.
One. How to find duplicate values in two columns in Excel?
1. Suppose we want to find out the duplicate values in two columns in a table. Select cell D1, copy and paste the formula =IFERROR(VLOOKUP(B1,A:A,1,0),"") to C1, press Enter on the keyboard, then it will return empty; Move the pointor to the fill handle of cell in the lower right corner of C1, hold down the Left Button and drag down, in the passing rows, both columns of AB with duplicate data will return duplicate data, and those without will return blank.
2. Explanation of the formula
The formula consists of two functions, IFERROR and VLOOKUP. IFERROR is an error judgment function, which is used to determine that after VLOOKUP is executed, if an error is returned, it will be displayed empty (i.e., "" in the formula); If a normal value is returned, nothing is returned, and the return result of VLOOKUP is displayed directly.
B1 is the value found for VLOOOKUP,
A:A is the range found,
1 is the value that returns the first column (i.e., column A),
0 is the exact match.
Two. How to find duplicate values in different Excel sheets?
There are two clothing sales tables with duplicate data (one in "Closthing September.xlsx" and the other in "Closthing October.xlsx"), and we need to use Vlookup to find the duplicate records. The method is as follows:
1. Add a AUX column to both tables to indicate rows with duplicate records. Fill 1 in the AUX column in "Closthing September.xlsx".
2. Switch to "Closthing October.xlsx", enter =IFERROR(VLOOKUP(A2,; Select the "View" tab, click "Switch Windows", select "Closthing September", switch to the "Closthing September" window, click Sheet1 in the lower left corner, select the "View" tab, click "Switch Windows", select "Closthing October", switch back to the "Closthing October" window, [Closthing September.xlsx]Sheet1! Auto-populated to the end of A2, the formula has changed to =IFERROR(VLOOKUP(A2,[Closthing September.xlsx]Sheet1!, continue typing $A2:$E8,5,0),""), then the full formula is =IFERROR(VLOOKUP(A2,[Closthing September.xlsx]Sheet1!$A2:$E8,5,0),"") , press Enter on the keyboard, it returns 1, and move the pointor over the fill handle of cell and drag down to find all duplicate records (Rows that return 1 are duplicate records).
3. Explanation of the formula:
=IFERROR(VLOOKUP(A2,[Closthing September.xlsx]Sheet1!$A2:$E8,5,0),"")
The formula also consists of two functions, IFERROR and VLOOKUP, and the IFERROR does the same thing as "vlookup finds duplicates in two columns" above. The found value of the VLOOKUP is A2; The found range is the $A2:$E8 (i.e., every column and row of the found table) of another document (i.e., the Sheet1 of the [Closthing September.xlsx] document), $A2 means that column A is absolutely referenced, and the "rows" are relatively referenced, i.e., when the formula is executed, the columns are not changed, but the rows are changed, and $E8 and $A2 mean the same thing; A column number of 5 is returned; 0 indicates an exact match.
Watch video How to find duplicate values in different Excel sheets and two columns in Excel online without registration, duration hours minute second in high quality. This video was added by user Loinsure 14 November 2023, don't forget to share it with your friends and acquaintances, it has been viewed on our site 241 once and liked it 4 people.