There are many ways we can do this, but it depends on the data structure and what the user wants as the final output. The spreadsheet can be complex by having more than one column or have only one column. For a simple data set, we can use basic options. Use matches Formula =MATCH(A2,$E$2:$E$7,0) That's comparing two columns I will show you in the image.
Steps to matching columns in different sheets
1. Open the two worksheets.
2. Write the formula matching formula. As for me, MATCH(A2,$E$2:$E$7), lock it by pressing f4 and then add the zero.
3. For the whole thing, you can drag it down for the Formula to work automatically for the rest of the cells.
4. The output will be easy; if you match, you will get the number, and if not, you will get #N/A. The numbers will show the two numbers are in both columns.
5. You can change the Formula to show you TRUE or FALSE. It's easy to add ISNUMBER before the match formula. =ISNUMBER(MATCH(A2,$E$2:$E$7,0))
6. The output will be TRUE for the matching data cells, and FALSE will be for the numbers that do not match.
Note: remember to lock the Formula. If the range is not locked, you won't get the full result.
1. Replace the match formula with VLOOKUP((A2,$E$2:$E$7, 1,0). The Formula is bout columns, so you can compare the only column by adding one at the end, and for the output, the result adds zero.
2. Close it and hit enter.
3. Drag the Formula down to apply for the whole column.
4. The result will be the exact data value between the columns, unlike match values. If they don't match, you will get #N/A.
5. For strings or texts, use ISSERROR. Put this Formula in front of the look formula.ERROR(VLOOKUP( A2,$E$2:$E$7, 1,0))
6. The output will be TRUE or FALSE but vice versa from reality. The matched value will be FALSE, and the TRUE will be not matching as shown in the figure.
7. To reverse ISSERROR add NOT before ISSERROR to get the actual result.
8. NOT(ISSEROR(VLOOKUP( A2,$E$2:$E$7, 1,0))). Then drag it down to apply the formula for the whole column.
FOR 3 COLUMNS IN DIFFERENT SHEETS.
When the sheet is big enough, the Formula is the same as the first procedure, but now the data range will vary. Even the array selected.
1. Open the two spreadsheets.
2. IN on one of the spreadsheets, use one column to write your Formula.
3. Use the IF statement.
4. =IF(ISSERROR(MATCH(A2,[wite the name of the sheet as for me is distributor 1.xlsx]sheet1!$A$2:$12,0 (0 is for exact match)))
5. The Formula will output true and false statements but still, you can add other features like unique or duplicate.
6. Click ok and auto-fill for the rest of the column.
IT can work for many worksheets, but people use some add-on to auto-complete their formulas. Some ads enable them to reuse the Formula in the future automatically that favors people who work with this kind of work every day.