Excel how to find values in 1 column exist in the range of values in another Excel how to find values in 1 column exist in the range of values in another vba vba

Excel how to find values in 1 column exist in the range of values in another


This is what you need:

 =NOT(ISERROR(MATCH(<cell in col A>,<column B>, 0)))  ## pseudo code

For the first cell of A, this would be:

 =NOT(ISERROR(MATCH(A2,$B$2:$B$5, 0)))

Enter formula (and drag down) as follows:

enter image description here

You will get:

enter image description here


Use the formula by tigeravatar:

=COUNTIF($B$2:$B$5,A2)>0 – tigeravatar Aug 28 '13 at 14:50

as conditional formatting. Highlight column A. Choose conditional formatting by forumula. Enter the formula (above) - this finds values in col B that are also in A. Choose a format (I like to use FILL and a bold color).

To find all of those values, highlight col A. Data > Filter and choose Filter by color.


You can use Advance Filter, which is in the Data tab. I'm assuming here that you want to check which entries in column A are in column B (as your illustration).First, your data must have headers.Second step, create the criteria range, which is the entries in column A with the header of column B (your are telling excel to which column applies the criteria)In "List range" press arrow and select range B, including header.In criteria range, press arrow and select the range that you created in second step, including header.Press OK. What you see now are the entries which satisfies the criteria or entries in column A which are in column B. To show all the data again, go to Sort & Filter and press the Filter button.You also have the option of opening a second worksheet and extract the data to this sheet. Create the criteria in this sheet, which is the entries in column A with the header of column B as above . Start in this new sheet, select Advanced and choose "Copy to a second location". "List range" is the range of column B so that you must select the first worksheet to select range B. For the Criteria range, press the arrow and select the criteria range you have just created.For "Copy to" select a cell 2 rows below the bottom of the criteria range.Press OK. You will have an extraction of the entries in columns A which satisfies the criteria for column B, that is , appear there.Now, you can delete the rows of criteria range to bring the data to top of worksheet.