Compare a cell to a range of cells for a match

[pigsfoot]

Hi,
We have an ANPR camera which provides an export of vehicle registration passing through our gate with the vehicle direction, i.e. In or Out.

All in bound vehicles are in column B and all outbound vehicles are in column H. If possible i would like a formula that checks all registrations in column B against all registrations in column H and tells me how many match.

I did try =If(B2=H2,"match","") but that obviously only checks the cells against each other on the same row, what i need is to chek B2 against the entire column H i.e. H2:H400. I did try to replace H2 with H2:h400 but got a weird error

Is this possible and if so coudl someone share the formula? Thanks

 

[sandy666]

Use Power Query, merge these columns then group and count

 

[pigsfoot]

Would you care to expand on how to do that?

 

[sandy666]

1. update your profile (Account details) about Excel version & OS
2. use XL2BB to post representative source data and expected result

 

[pigsfoot]

I used this formula in the end which seems to be working - =SUMPRODUCT(--(ISNUMBER(MATCH(B2:B400,H2:H400,0))))

 

[sandy666]

sure , no problem
Have a nice day

but post#4 point 1