'How to search for one value in a column against all values in multiple columns in Excel

I tried a lot to find a solution for the below issue, lets say I have column A,C,D ; I want to take the fist value in column A and check if it exist in C or D entire columns; if exist then I will return A, then take the second value in A and search in C & D columns and so on.

The bottom line, is that I want to find the count of each value in column A that existed in C or D

Thanks

excel excel-formula

Solution 1:^[1]

=COUNTIF(C:C,A1:A4)+COUNTIF(D:D,A1:A4)

Solution 2:^[2]

Using SUMPRODUCT Function

Formula used in cell B2

=SUMPRODUCT((A2=$C$2:$C$7)+(A2=$D$2:$D$7))

And Fill Down!

Sources

This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.

Source: Stack Overflow

Solution	Source
Solution 1	P.b
Solution 2	Mayukh Bhattacharya

'How to search for one value in a column against all values in multiple columns in Excel

Solution 1:[1]

Solution 2:[2]

Sources

Related Questions

Solution 1:^[1]

Solution 2:^[2]