Forum Discussion
Return multiple rows with matches
I am trying to compare a variable set of numbers that will be pasted in by a user with two different columns in a table and then outputting the rows that match in either column. I would like to eventually delete the duplicates this produces...but i cant even figure out the first part.
I've tried combinations of V and X lookups, Index, Match and other random things to no avail.
see below for an example of what I'm trying to achieve.
3 Replies
You may try free CompareSheets (without space) add-in from Microsoft Store, it has also a built in algorithm that identifies the unique identifiers, as well has an option you can exclude from comparison (but not from output) columns that you do not want to track, will show what was changed in the rows and columns and therefore are merged in one output. I think that is what you are looking for.
To expand upon Hans' reply, see the attached workbook.
=LET(v, FILTER(A3:J9, ISNUMBER(XMATCH(E3:E9, M2:M6))+ISNUMBER(XMATCH(F3:F9, M2:M6)), ""), IF(v="", "", v))
