Index-Match on Multiple Columns -- Nth Result

[Bratamoli]

Can someone help me adjust this formula to return the Nth "group" that a "name" is in? And if the "name" only occurs once, the search for the 2nd occurrence will return ""?
Sticking with the pictured example, I would like each person's 1st group to show in column H [like it already is] and each person's second group--for those that have a 2nd group--to show in column I.

In my current data-set, nearly every "name" occurs in 2 different "groups"; in my next data-set, most "names" will occur up to 6 different times.

{=INDEX(groups,MATCH(1,MMULT(--(names=G4),TRANSPOSE(COLUMN(names)^0)),0))}

Index and match on multiple columns
<hyperlinked image="">

</hyperlinked>

 

[Aladin Akyurek]

Try to post this in an Excel readable form, not as an image.

 

[Bratamoli]

Thank you for the Reply, Aladin.

The original was the picture on the website I found the partial-answer on:
https://exceljet.net/formula/index-and-match-on-multiple-columns

Not sure how to "post this in an Excel readable form," so I did this.
I recreated the table in Excel and copy-pasted it to here.
You can copy-paste it back into excel and it will work.
Paste this array formula into cell H3, then fill it down through cell H14:
{=INDEX(groups,MATCH(1,MMULT(--(names=G3),TRANSPOSE(COLUMN(names)^0)),0))}

Again, the goal is to return the Nth "Group" that the name is in.
For example (and I changed the name in cell E5 for this), cell H9 should return "Fox" (the 1st group Luis is in) and I9 should return "Moose" (the 2nd group Luis is in).

[TABLE="class: grid, width: 512"]
<tbody>[TR]
[TD]A1[/TD]
[TD]B[/TD]
[TD]C[/TD]
[TD]D[/TD]
[TD]E[/TD]
[TD]F[/TD]
[TD]G[/TD]
[TD]H[/TD]
[TD]I[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]Group[/TD]
[TD]Name 1[/TD]
[TD]Name 2[/TD]
[TD]Name 3[/TD]
[TD][/TD]
[TD]Name[/TD]
[TD]Group[/TD]
[TD]Group 2[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]Fox[/TD]
[TD]Doug[/TD]
[TD]Luis[/TD]
[TD]Katy[/TD]
[TD][/TD]
[TD]Adam[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD]Bear[/TD]
[TD]Adam[/TD]
[TD]Susan[/TD]
[TD]Alex[/TD]
[TD][/TD]
[TD]Alex[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]5[/TD]
[TD]Moose[/TD]
[TD]Aya[/TD]
[TD]Mary[/TD]
[TD]Luis[/TD]
[TD][/TD]
[TD]Aya[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]6[/TD]
[TD]Wolf[/TD]
[TD]Juan[/TD]
[TD]Victor[/TD]
[TD]Mia[/TD]
[TD][/TD]
[TD]Doug[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]7[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]Juan[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]8[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]Katy[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]9[/TD]
[TD="colspan: 2"]names = C3:E6[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]Luis[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]10[/TD]
[TD="colspan: 2"]groups = B3:B6[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]Mary[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]11[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]Mia[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]12[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]Rie[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]13[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]Susan[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]14[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]Victor[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

[TABLE="class: grid, width: 512"]
<tbody>[TR]
[/TR]
</tbody>[/TABLE]

 

[lrobbo314]

This seems to do it.

=IFERROR(INDEX(Groups,SMALL(IF(ISNUMBER(SEARCH($G3,Names)),ROW(Groups)-1,""),COLUMN()-7)-1),"")

Array formula so Ctl+Shift+Enter

 

[Bratamoli]

@Irobbo314

You are a Genious!!
Thank you.

 

[Bratamoli]

It needs some troubleshooting though.
I tried to troubleshoot it myself, but I wasn't successful with it.

Here's a link to the file in Dropbox: https://www.dropbox.com/s/yad78jy781fqh4q/Pokémon GO.xlsx?dl=0

In the "Pokémon" tab, column AP (using the "exceljet.net Formula" below) looks for the character's number (from column D) in the cell-set "FMPoké" ('Fast TMs'!I6:BO68) and returns the move name from cell-set "FMs" ('Fast TMs'!C6:C68). It doesn't work for all of them yet--and it only returns the 1st result found--but the results it returns are correct. I could troubleshoot this one to work for all of them, but I really need a formula that will return each result vs. only returning the 1st result.

In the "Pokémon" tab, column AQ (using "lrobbo314's Formula" below) seems to be working except that it returns the wrong move name. [Edit: it also only works for about the first dozen rows.] Irobbo314's Formula worked perfectly in the sample spreadsheet ('Sheet 1' in my workbook) from earlier in this thread--to include showing the next "group" a "name" was in when I filled it across the other columns. I even tested it prior to my previous reply in this thread by adding a 3rd results column and changing several of the names.

exceljet.net Formula:
{=INDEX(FMs,MATCH(1,MMULT(--(FMPoké=[@['#]]),TRANSPOSE(COLUMN(FMPoké)^0)),0))}

lrobbo314's Formula:
{=IFERROR(INDEX(FMs,SMALL(IF(ISNUMBER(SEARCH([@['#]],FMPoké)),ROW(FMs)-1,""),COLUMN()-7)-1),"")}

Thank you again for your help with this.

 

[Aladin Akyurek]

Try to post the correct results against which the candidate formulas including those of post #6 can be evaluated.