Forum Discussion
How to search if a values exists, where, and how many time it is found in a list
I have two lists. I need to search each value of list 1 to see if, where and how may times it exists in list 2.
I tried MATCH which gave me the index number of where my match was found, but the issue is that sometimes the value exits more that once.
I need to know when that happens and on what line I can find those values
=IFERROR(SMALL(IF($A$1:$A$24=$C2,ROW($A$1:$A$24)),COLUMN(A:A)),"")Maybe with this formula. Enter the formula with ctrl+shift+enter if you don't work with Office 365 or Excel 2021. The formula is in cell D2 and filled across range D2:N5.
- Thanks! I think this could work, but I need a way to cop out the values from column E into column D while removing duplicates. Something like copy if not a duplicate
Perhaps you can give an example of the data and of the expected result (without sensitive information). Which version of Excel do you work with? If you have Office 365 or Excel 2021 or Excel for the web then the UNIQUE function could be helpful.
Or does the example in the screenshot already show what you want to do?
