Returning multiple entries with INDEX and MATCH

Hi,

A formula can only return one value. Try using Data Filter instead.

HTH

How can I use a data filter when I'm pulling values from another file? I'm not sure how to do this.


Thanks,
Jesse

Originally Posted by jessea

How can I use a data filter when I'm pulling values from another file? I'm not sure how to do this.


Thanks,
Jesse

Hi,

In exactly the same way as you would filter data within the same workbook. Make sure you have the source workbook open, then starting in the destination workbook make sure you have exactly the same column labels as the source workbook, (you can have fewer but not additional labels), and use the Data-->Filter-->Advanced Filter from the menu, picking the 'Copy to Another Location' option, then click the 'List Range box' drop down arrow which will allow you to navigate over to the source workbook and highlight the range you want to filter (including column labels), then click the drop down arrow again, and in the destination workbook click the drop down arrow for the 'Copy To' option, select the range to which you want to filter the records and click OK.

If you want to use a filter criteria to select a sub set of your records, then just put the appropriate columns labels in the destination WB with the criteria values underneath them, and use the 'Criteria Range' drop down arrow to select the criteria range.

HTH

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!

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!

Originally Posted by Domenic

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!

Hey,

I tried to implement this code and I'm having a little bit of trouble. I can make it work if I use an example just like the one provided but when I try to make it fit my database, it doesn't work. Can you please elaborate on what exactly is going on in the formula? What it's doing and the theory behind it? I'm sure that if I have a better understanding of the fundamentals of this formula, I'll be able to make it suit my needs.

Thank you,
R

I found an answer to my own question, haha.

http://office.microsoft.com/en-us/ex...001226038.aspx

Your post does not comply with Rule 2 of our Forum RULES. Don't post a question in the thread of another member -- start your own thread. If you feel it's particularly relevant, provide a link to the other thread.