I have an excel file that contains multiple columns and many rows. Two of those columns are phone number and date.

What I would like to accomplish is to check if two rows share the SAME date and the SAME phone number. This could be a function in another column that outputs the index of the rows that share the same data or 0 if there is no found index.

I know how to do this in SQL, or any programming language, but for some reason excel has me stuck on this for quite a bit of time. This is the function I tried to use, but I fear the results are inaccurate: IF(AND(MATCH(E2,E3:E4761,0)MATCH(I2,I3:I4761,0)),1,2)

Column E represents the phone number and I represents the date.

I tried to output 1 if there was a match and 2 if not to begin with, but it's too general because I need it to check the same row each time and get all the available results.

Thank you for your time, I would appreciate any help on the matter.

CodePudding user response：

Your formula looks perfectly fine to me except for a missing comma. It should be this:

IF(AND(MATCH(E2,E3:E4761,0),MATCH(I2,I3:I4761,0)),1,2)

Or, you can use CountIf function to replace Match

IF(AND(COUNTIF(E3:E4761,E2),COUNTIF(I3:I4761,I2)),1,2)

CodePudding user response：

If you don't mind including the row that the formula is in in your list, and if you have Microsoft Excel 365 with the FILTER function, you can use:

=TEXTJOIN(", ",TRUE,FILTER([Column1],[Column2]=[@Column2],[Column3]=[@Column3]))

Note that I used a Table and structured references