# Returning Multiple Entries With Index And Match

• I'm trying to put together a spreadsheet that gets its data from another Excel file.

Here is the formula I'm using currently:

=INDEX([other_file.xls]Sheet_1!\$A\$2:\$A\$1000,MATCH(\$M\$1,[other_file.xls]Sheet_1!\$E\$2:\$E\$1000,0),1)

It returns the first match just fine, but if I copy it down to the rows below it just gives me back the same match (obviously). Is there a way to modify this formula so that it checks for results already given and only gives unique ones in every row?

I posted this same question on another forum as well: http://www.excelforum.com/showthread.php?t=644756

Filters are not an option for me on this project. Any help would be greatly appreciated. Thanks a lot!

Jesse A.

• Re: Returning Multiple Entries With Index And Match

Try the following array formula (validate with CTRL+Shift+Enter) :
=IF(ISERR(SMALL(IF(N([other_file.xls]Sheet_1!\$E\$1:\$E\$2000=\$M\$1)=1,ROW(\$1:\$2000),FALSE),ROW())),"",INDEX([other_file.xls]Sheet_1!\$A\$1:\$A\$2000,SMALL(IF(N([other_file.xls]Sheet_1!\$E\$1:\$E\$2000=\$M\$1)=1,ROW(\$1:\$2000),FALSE),ROW()),1))
HTH
Daniel

Regards.
Daniel

• Re: Returning Multiple Entries With Index And Match

Awesome! It works great, except for one thing. If I don't start it on Row 1 of my spreadsheet, it doesn't give me full results. I need to start it on Row 5, and I'm not sure what to change in the formula to accomodate this. Does that make sense?

Thanks a lot,
Jesse

• Re: Returning Multiple Entries With Index And Match

jesse,

MODERATOR NOTICE: This topic has also been posted on other sites and may already have an answer elsewhere. Please take this into consideration when answering this question

*

http://www.excelforum.com/showthread.php?t=644756

PM Dave Hawley if you agree to the forum rules

VBA Noob

## Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!