0

I have two sheets in Google Sheets. In one I have two columns with values I need to find in two columns of the second one and return number of matches for each row in the first one.

=Iferror(INDEX ( Leads!$C$2:C$1000, ( MATCH ( $A2 & $B2,Leads!$A$2:$A$1000 & Leads!$B$2:$B$100, 0 ))),0)

I've been using this function and then assigning it 1 or 0 values with IF function, but it works only for a range without more than one match.

How to do it when there are more matches?

1

You have not mentioned what format your data is in, but please try:

=COUNTA(QUERY(Leads!A:B,"SELECT A WHERE A = '"&Sheet1!A1&"' and B = '"&Sheet1!B1&"' "))

copied down to suit.

COUNTA QUERY

| improve this answer | |
1

It seems like a countif formula would work from what you described.

If I have misunderstood your question, please share a screenshot or example of your data.

If you're specifically looking for rows which match data in both columns, you could add a helper column in column C for both sheets: =Concat(A1,B1) in cell C1 and drag down. Then in Cell D1 of Sheet 1, use this formula:

=Countif('Sheet2!'C$1:C$1000,C1)

And drag down.

| improve this answer | |

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.