Forum Discussion
UXMcM
May 10, 2023Copper Contributor
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 is...
OliverScheurich
May 10, 2023Gold Contributor
=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.
UXMcM
May 10, 2023Copper Contributor
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
- OliverScheurichMay 10, 2023Gold Contributor
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?