Return multiple instances of matching data

[Franky17]

I have looked on this forum and been unable to find it. Essentially I am trying to look in another spreadsheet array, find where the data matches a specified cell in the orginal spreadsheet, and return the data. I accomplished that by using:

=INDEX('[Sheet2.xls]Sheet 2'!$K$3:$K$54,MATCH($A$1,'[Sheet2.xls]Sheet 2'!$G$3:$G$54,0))

where A1 is the data I am trying to match with column G, and k is the value I want returned. My problem comes in because there are multiple instances of A1 in column G, and I'm greedy I want them all.

I tried to rewrite the formula to start the index array over at the row the last data was last found +1. That way I could copy it down and it would return results until there were no more instances. So instead of
'....$K$3:$K$54...'
it would be $K$(?):$K$54

So

A B
A1 1st Instance of match data in corresponding row K
2nd Instance data in corresponding row K
3rd Instance data in corresponding row K

I hit a wall with my excel knowledge, is there a way?

I appreciate your help!

 

[Robert Mika]

Hi Franky and welcome to the forum.

Lookk here

http://www.mrexcel.com/forum/showthread.php?p=2932943#post2932943

or here:
http://www.mrexcel.com/forum/showthread.php?t=592750

You just need to adjust your ranges accordingly.

 

[Franky17]

Thanks! That worked perfectly.

However, it only works correctly when the reference spreadsheet is open on my computer. The formula references the correct drive, folder, and file but returns a #VALUE until I open the second spreadsheet. Is there a fix for this without using macros?

 

[BenMiller]

Sounds like you need to use a VLOOKUP. Copy the sheet you want to read from to the same workbook as the one you are working on.
Make sure the data you are trying to match is in the first column of your range, and sorted ascending.

 

[Franky17]

Thanks Ben, but I want the two files to be separate. The reference spreadsheet will be updated periodically and I want the first spreadsheet to update with that information when opened.

Unless I am going about this the wrong way... I have one sheet, the reference sheet, that has a combined amount of data which has multiple instances. I need a separate file which for every instance in my combined spreadsheet is a new sheet in that file which pulls in data from the reference sheet, as well as other information that may be manually entered.

So if the reference file has

A1 - Date 1, Explanation 1
B1 - Date 2,Explanation 2
C1 - Date 3,Explanation 3
D1 - Date 4,Explanation 4
A1 - Date 5,Explanation 5

The created file will have a Sheet for each : A1, B1, etc.

On Sheet A1 will be

Date 1, Explanation 1
Date 5, Explanation 5

If there is an automated way I can create a new sheet for every Instance in the reference file that would be great as well.