Excel two columns of data, to find the same item. How to find out quickly with functions?

Take a look with the vlookup function.

Please insert column C after column A and column B

Then, enter the formula in cell C1 in column C

vlookup(b1,$a$1:$a$8000,1,false) means that when b1 is the same as a song name in "A1 to A8000", the song name is returned to cell C1. The concept of absolute quoting is used in this formula, which means that when you drag a formula to cells such as c2 and c3, the range will not change by copying the formula, "$ is what it does."

For example, the above formula, when copied to cell C2 by dragging the mouse, would be:

vlookup(b2,$a$1:$a$8000,1,false)

Sort the songs in ascending order by column a, enter "=vlookup(b1,a:a,1,false)" in c1 and fill in the formula downwards, showing that the song titles are duplicates in two columns.

In the first box of column C, enter =vlookup(a1,$b$1:$b$8000,1,false).

It is the result of finding A1 in B1 to B8000 in C1.

Then pull it down and you're good to go.

Click the "Advanced filtering" icon. Click "Copy filter results to another location". Select "List Area" and click on a cell range.

Select "Conditional Region" and click another cell range. Select "Copy to", click any cell, and then click "OK". Excel has looked up the same data and extracted it, detailed steps:

1. Open the computer**, and click the "Advanced Filtering" icon in the "Data" interface.

2. In the Advanced Filtering window, click "Copy Filtering Results to Other Locations".

3. Select "List Area" and click a cell range.

4. Select "Condition Region" and click another cell area.

5. Select "Copy to", click any cell, and then click "OK".

6. Excel has found the same data and extracted it.

Here's how:

Operating Equipment: Dell Computer.

Operating system: win10

Operating software: excel

1. First of all, enter two sets of data that need to be compared in excel**, and there are duplicate data in the two groups of data that need to be selected.

2. Then enter the function formula in cell C1: =if(countif(a:a,b1),"Repeat",""), which means that the data from b1 is used to find out if there are duplicates in column A.

3. Click Enter to see that the B1 cell of the comparison has duplicate data in column A, so the word "repeat" is displayed in the C1 cell.

4. Finally, fill the formula in the cell downward, you can see that the cells with duplicate data in column A in column B will be marked with words, and these cells can be singled out.

1.Select an empty cell, and click Advanced Filtering in the Sort and Filter group on the Data tab. If it is Excel 2003, click the menu "Data Filtering Advanced Filtering".

2.Set the List Area to the region where one of the columns of data is located, in this case C1:C127; Set the Conditional Region to the region where the other column of data is located, in this case A1:

a98。Since you want to extract the same name in two columns to column e, this example is selected.

3.Click "OK", excel can extract the same name in both columns to column E.

Operating Equipment: Dell Computer.

Operating system: win10

Operating software: excel2017

1. First of all, open a ** with data inside, as shown in the following figure:

2. Select cell F3 and enter "=" in the cell, as shown in the following figure:

3. The [Insert Function] dialog box pops up, then select the "if" function in "Select Function", and then click the [OK] button.

4. Move the cursor to the dialog box after the first parameter and enter "countif(b3:b12,e3)>0";

5. Move the cursor to the second parameter and enter "same" as shown in the figure below

6. Move the cursor to the third parameter and enter "Different", and click the [OK] button after all the parameters are set, as shown in the following figure

7. Return to the worksheet to check the data of the two columns, as shown in the following figure:

1. Open the Excel** that needs to be checked for duplicates, and select the cell series that needs to be compared.

2. Click on the "Conditional Formatting" option in the toolbar at the top of the page.

3. Click "Highlight Cell Rules" in the pop-up options.

4. Then check the "Duplicate Value" option.

5. Click "OK" in the pop-up dialog box.

6. You can mark out the duplicate values in the selected cells.

If you're the 07 version.

Select these two columns of data.

Point Start - Conditional Formatting - Highlight Cell Rules - Repeat Value, select the color you want and it's OK.

Start - Conditional Formatting - Highlight Cell Rule - Duplicate Value.

Different ways to find duplicates in different two columns in excel:

Different versions of Excel have slightly different operation methods, but they are similar, the following takes Excel2007 as an example.

1.Open the required excel.

Observation** found that some of the data were identical, and the duplicate data needed to be highlighted in a bold color. First, select a column or region where you want to find duplicate values.

2.Click the mouse on the Start menu, select Style - Conditional Formatting - Highlight Cell Rules - Duplicate Values.

3.The Duplicate Values dialog box is popped up to format cells that contain the following types. The default selection is that the repeat value is set to light red, fill color, dark red text. Click OK.

How exactly do you find the same items in two columns of data in Excel? Here's how I brought me to find the same items in two columns of data in Excel**, I hope you will be inspired after reading it!

Excel's method of finding the same item in two columns of data:

Find the same item Step 1: As shown in the following two columns of data, we need to find the same data as him. Start by selecting Conditional Formatting under the Start Menu.

Find the sameStep 2: Click on the box that appears after clicking, select the first one: highlight the cell rule, and then select the duplicate value.

Find the same Step 3: After you select it, he will prompt you to set the format, there are many setting options, you can also choose a custom format, and you can set it in more detail.

Click the "Advanced filtering" icon. Click "Copy filter results to another location". Select "List Area" and click on a cell range.

Select "Conditional Region" and click another cell range. Select "Copy to", click any cell, and then click "OK". Excel has looked up the same data and extracted it, detailed steps:

1. Open the computer**, and click the "Advanced Filtering" icon in the "Data" interface.

2. In the Advanced Filtering window, click "Copy Filtering Results to Other Locations".

3. Select "List Area" and click a cell range.

4. Select "Condition Region" and click another cell area.

5. Select "Copy to", click any cell, and then click "OK".

6. Excel has found the same data and extracted it.

How to use Excel to quickly retrieve information? This article will show you how to use Excel to find two columns of the same data.

Material tools.

Excel 2010 method.

First, select the contents of the two columns and click Conditional formatting in the top right.

Please click Enter a description.

In the tooltip, click the highlight cell rule, and the word equals.

Please click Enter a description.

Enter the content of the cell you want to find in the text box, for example, Wang Chao.

Please click on the slag to enter **, as described by Sun Na.

Please click to enter **Description.