Finding the total number of matches within two columns?

[mikexcel]

I have a sheet, i need to compare two of the columns (numerical), and pull out the total of the numbers that match in both columns. I cannot imagine it's that difficult, however i'm not that good at typing the right question into their help menu, nor are the results very detailed. I have asked people about VLookup, but have not found something as simple as bringing back one single number, i find mostly complicated lookup instructions, which leads me to believe this is even more simple than i think. Can anybody help with the VLookup formula for this, or another more simple route?

[starguy]

I have a sheet, i need to compare two of the columns (numerical), and pull out the total of the numbers that match in both columns. I cannot imagine it's that difficult, however i'm not that good at typing the right question into their help menu, nor are the results very detailed. I have asked people about VLookup, but have not found something as simple as bringing back one single number, i find mostly complicated lookup instructions, which leads me to believe this is even more simple than i think. Can anybody help with the VLookup formula for this, or another more simple route?

if you have two columns A and B with your data, then in col C try this

=IF(ISERROR(MATCH(A1,$B$1:$B$500,0)),"",A1) and copy it down in col C
it will show you only those values which are in both columns.

[mikexcel]

col A has a great deal more values and col B does, i copied the formula, it seems to work for col B's matches (col B values that are in col A), but i tried the same to compare the longer list to the shorter (col A values that are in col B), that results in all blank cells. Should this not at least have as many matching values as the first time i ran the 'IF'? *Note, i also have some columns hidden that have text in them, to allow to better view just the two numerical columns, if i should unhide everything, let me know. Appreciate the help.

[mikexcel]

Also in the formula, when copied, its adding increments to the lookup column each time also, therefore not comparing the column to any of the rows above the last row, how do i prevent this, i need each value to be compared to the entire list, rows above / rows below?

[starguy]

Also in the formula, when copied, its adding increments to the lookup column each time also, therefore not comparing the column to any of the rows above the last row, how do i prevent this, i need each value to be compared to the entire list, rows above / rows below?

try this
=IF(ISERROR(MATCH(B1,$A$1:$A$500,0)),"",B1)

range should have absolute references like $A$1:$A$500 to avoid increments.
(adjust col A range if it contains more data)

[mikexcel]

Thank you again for your help!