I've been getting stuck a lot lately when it comes to sheets and I'm having an issue where I have a sheet that has names and ID's (A1:A, B1:B) in the same sheet I have a list of some of the same names and a PIN (C1:C, D1:D). I would like to use sheets to compare the names and if they are the same then post in column E the name, in column F the ID, and column G the PIN. When I try doing so, I keep getting an error.

I have tried using a formula that is similar to this =filter(A2:B,ISNUMBER(match(A2:A,A3:A,0))) but an error will occur/ when changing it won't give the data needed. I have posted a picture of what the solution I'm looking for would look like. Please note that the names, ID and PINs are all automatically updated so when a new name is added the sorting still needs to work. enter image description here

CodePudding user response：

You can try with this:

=LAMBDA(common,{common,BYROW(common,LAMBDA(each,VLOOKUP(each,A:B,2,0))),BYROW(common,LAMBDA(each,VLOOKUP(each,C:D,2,0)))})(query(query({A:A;C:C},"SELECT Col1,COUNT(Col1) where Col1 is not null group by Col1"),"SELECT Col1 where Col2 > 1"))

If you need some kind of additional sorting you can wrap all this in SORT function