CODEWITHSUNDEEP
excelexcel-formulavlookup
Vinay Dalal
Vinay Dalal

Reputation: 1

How to check for any matching cells in two columns for excel?

I have an excel file containing multiple sheets. The first sheet named = " Main " is what I am working on and the sheet named = " Data " contains the back data, using which I am building a symmetric matrix. Eg showed in the image for both.

I want to check if the intersection cells in the "Main" sheet have any matching values in their respective columns in sheet "Data" and get 1 (if yes) or 0(if no matching cell value) found.

For eg, if in "Main" Sheet I am at Column E and Row 4 ie E4, so if want look for the column named as the header of column E and row 4 in the "Data" sheet and check if any of their cells have matching contents.

Link for sample data : https://docs.google.com/spreadsheets/d/1W5Nj91lVUt0NAm_h6KkONucoy2sXMGjJ3z-Tl1K1QhY/edit?usp=sharing

Upvotes: 0

Views: 200

Answers (1)

Michal
Michal

Reputation: 6013

I think the simplest way to accomplish it, would be to use named ranges. You can name every column on the Data sheet with the name of the player (without spaces - as I have done so on your file).

To calculate how many ODIs any two players have in common you can simply multiply their respective ranges (columns), for example:

=SUMPRODUCT(indirect(substitute(A6," ",""))=TRANSPOSE(indirect(substitute(G1," ",""))))

The indirect function resolves to:

=SUMPRODUCT(ManinderSingh=TRANSPOSE(ChetanSharma))

And gives you the total of matches those two played together: enter image description here

It would be possible to do it without the named ranges, instead looking up the player names dynamically but that would make a pretty messy and complicated formula (at least the way I have attempted it). Setting up named ranges might take you a few extra minutes but it's a far more elegant solution in my opinion.

Upvotes: 0

Related Questions