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

excel excel-2007 excel-formula excel-2010 vba
user1254579 picture user1254579 · Aug 28, 2013 · Viewed 275k times · Source

I have two columns- column A which extends upto 11027(values) and column I which extends to 42000(values).Both the columns contains some code details.

Something like this

A               B
q123           as124
as124          gh456
ff45           q123
dd1             dd2
xx2
xx3
xx4

and so on...

I want to find if the names in column A exists in column B or not.using excel

Answer

Mayou picture Mayou · Aug 28, 2013

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

Related questions

Excel: Searching for multiple terms in a cell

I use this handy equation to search for a term inside of a cell in excel. =IF(ISNUMBER(SEARCH("*Gingrich*",C1)),"1","") This equation searches for the presence of Gingrich in C1, if it exists, it displays a 1. All I'd like …

Read More
Excel formula to reference 'CELL TO THE LEFT'

I'm trying to do conditional formatting so that the cell color will change if the value is different from the value in the cell left of it (each column is a month, in each row are the expenses on certain …

Read More
PivotTable to show values, not sum of values

I'm wanting to display a pivot table and for it to show me the actual values, one on each row, rather than a sum of the values. E.g. Name Jan Feb Mar Apr Bob 12 10 4 3 5 James 2 6 8 1 15 etc. My starting point …

Read More