Hi,
I have a two columns of names, different length, and want to come up with a third column which lists all the matching matching values of columns 1 and 2. Any idea of how to do that?
Hi,
I have a two columns of names, different length, and want to come up with a third column which lists all the matching matching values of columns 1 and 2. Any idea of how to do that?
You havent stated what you expect the output to look like and I can see several ways of outputting this.
Post a sample spreadsheet with expected results, remove any sensitive data, create a mockup example if necessary.
Use the Go Advanced option at the bottom of the page then scroll down to Manage Attachments as the "paperclip" method does not work on this forum.
Regards
Special-K
Ensure you describe your problem clearly, I have little time available to solve these problems and do not appreciate numerous changes to them.
Capture.PNG
Just like that
I cant get this working without a helper column
Your blank top row and column implies the data starts at B3 (Billy).
in D3
=IFERROR(VLOOKUP(B3,C$3:C$6,1,0),"")
in E3
=IFERROR(INDEX($D$3:$D$8,AGGREGATE(15,6,ROW($D$3:$D$8)/(($D$3:$D$8<>"")),ROWS(A$3:A3))-(3-1),1),"")
This assumes the first column will be equal to or longer than the second column, if that's not the case you'll need to swap the B and C columns around in the formulas
Thanks, very helpful.
A B C D E 1a b c 2Billy Martin Jhon 3Jim Charles Martin 4Jhon Jhon 5Alfred 6Jack 7Martin
E2=IFERROR(INDEX($A$2:$A$100,AGGREGATE(15,6,ROW($A$2:$A$100)-ROW($A$2)+1/(ISNUMBER(MATCH($A$2:$A$100,$C$2:$C$100,0))),ROWS($A$2:A2))),"")
copy down
There are currently 1 users browsing this thread. (0 members and 1 guests)
Bookmarks