Returning multiple entries with INDEX and MATCH

Try...

N1:

=SUMPRODUCT(--([other_file.xls]Sheet1!E2:E1000=M1))

O1, confirmed with CONTROL+SHIFT+ENTER, and copied down:

=IF(ROWS(O$1:O1)<=$N$1,INDEX([other_file.xls]Sheet1!$A$2:$A$1000,SMALL(IF([other_file.xls]Sheet1!$E$2:$E$100=$M$1,ROW([other_file.xls]Sheet1!$E$2:$E$100)-ROW([other_file.xls]Sheet1!$E$2)+1),ROWS(O$1:O1))),"")

Hope this helps!

Hi Domenic,

Thanks for your help. I tried putting it into my spreadsheet but it didn't seem to work. I was wondering if you could explain to me what the formula is saying? I'm not very good at these yet...

Also, what do you mean by "confirmed with CTRL+SHIFT+ENTER"?

Thank you,
Jesse

Hi Domenic,

Thanks for your help. I tried putting it into my spreadsheet but it didn't seem to work. I was wondering if you could explain to me what the formula is saying? I'm not very good at these yet...

Also, what do you mean by "confirmed with CTRL+SHIFT+ENTER"?

Thank you,
Jesse

The formula needs to be confirmed with CONTROL+SHIFT+ENTER, not just ENTER. In other words, enter the formula, press the CONTROL and SHIFT keys down, then while these keys are pressed down press the SHIFT key. If done correctly, Excel will automatically place curly brackets {....} around the formula.

Hope this helps!