Index Match with multiple results with a twist

[michaeltsmith93]

Hi, I'm trying to use INDEX, MATCH and SMALL to return multiple results and having some difficulty. On Sheet1, I have the following data:

[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD]Study[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]Investigator[/TD]
[/TR]
[TR]
[TD]Study123[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]Smith, John[/TD]
[/TR]
[TR]
[TD]Study123[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]Smith, John[/TD]
[/TR]
[TR]
[TD]Study123[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]Smith, John[/TD]
[/TR]
[TR]
[TD]Study456[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]Smith, John[/TD]
[/TR]
[TR]
[TD]Study456[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]Smith, John[/TD]
[/TR]
[TR]
[TD]Study123[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]Doe, Jane[/TD]
[/TR]
[TR]
[TD]Study123[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]Doe, Jane[/TD]
[/TR]
[TR]
[TD]Study123[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]Doe, Jane[/TD]
[/TR]
[TR]
[TD]Study789[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]Doe, Jane[/TD]
[/TR]
</tbody>[/TABLE]


On Sheet2, the investigator names are listed with last and first names in separate columns, so I concatenate that and add a comma and put that in Sheet 3 Row 1, as below. In the subsequent rows, I'd like the formula to spit out the studies in Sheet1 Column A. It would look like this:

[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD]Doe, Jane[/TD]
[TD]Smith, John[/TD]
[/TR]
[TR]
[TD]Study123[/TD]
[TD]Study123[/TD]
[/TR]
[TR]
[TD]Study123[/TD]
[TD]Study123[/TD]
[/TR]
[TR]
[TD]Study123[/TD]
[TD]Study123[/TD]
[/TR]
[TR]
[TD]Study789[/TD]
[TD]Study456[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]Study456[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]


The formula that I have is: =IFERROR(INDEX(Sheet1!$A$2:$G$44474,SMALL(IF(Sheet1!$G$2:$G$44474=A$1,COLUMN($A$1:$GL$1)),ROW(1:1))-1,1),""). If I use ROW instead of COLUMN in the IF statement, I get the first result in Sheet1 for every cell. If I use COLUMN (which makes more sense), then it just returns blank for everything.



I would love a fix for this formula if possible.



If anyone could also offer guidance on how to only return each study once (eliminating the duplicates), that would be awesome.



Thank you!

 

[Fluff]

Try
=IFERROR(INDEX(Sheet1!$A$2:$A$44474,SMALL(IF(Sheet1!$G$2:$G$44474=A$1,ROW($A$2:$A$44474)-ROW(A$2)+1),ROWS($1:1)),1),"")

Confirmed with Ctrl Shift Enter

 

[michaeltsmith93]

Ah that fixed it! Can you please explain to me why you're subtracting row a2 and adding 1? And I assume that ROWS($1:1) accomplishes the same as ROW(1:1)-1?

Thank you again!

 

[Fluff]

It was pointed out to me the other day, that without those, if you insert a new row 1 it will cause problems with the formula

 

[michaeltsmith93]

Very clever. Thank you!

 

[Fluff]

You're welcome