Return multiple values across multiple spreadsheets - Excel 2010

[oppykm2]

Hi Excel Forum community,

I have a question that I cannot find an answer to, even after searching for about half an hour on this forum. I have found many existing topics on returning multiple values, but the functions do not seem to work for me. I apologize in advance if I overlooked a similar topic.

I have attached a spreadsheet for your reference. The red values are those that are intended to be derived from the formula.

Hopefully I can explain this without making it too complicated. The base data tab could have anywhere up to 50,000+ entries, made up of around 30 unique records (each of the 30 unique records would be sent to their own tab) - I've only included three records in my example .

In the example, I want to paste all of the Record1, Record2, and Record3 value's in their appropriate spreadsheet, referencing the information in the Control tab.

Using a simple vlookup as an example, it would look something like this:

=VLOOKUP('Control List'!$B$2,'Base Data'!A:B,2,FALSE), but obviously returning all the Record 1's from the base data tab. As I stated before, there could be tens of thousands of records so the formula would have to be flexible.

I've read up on the array functions, but still cannot seem to get them to work. Could someone spare some time to help me out? I would greatly appreciate it.

Thanks!

[Pete_UK]

Put this formula in C2 of your Base Data sheet:

=IF(A2="","-",A2&"_"&COUNTIF(A$2:A2,A2))

Copy this down at least to the bottom of your data. It doesn't matter if you copy it further - the hyphens will show you how far you have copied it.

Then you can put this formula in cell A1 of your Record1 sheet:

=IF(ISNA(MATCH("Record1_"&ROWS($A$1:$A1),'Base Data'!C:C,0)),"",INDEX('Base Data'!B:B,MATCH("Record1_"&ROWS($A$1:$A1),'Base Data'!C:C,0)))

This same formula can be placed in A1 of all your other sheets (a quick way of doing this is to group all those sheets together (from Record1 to RecordN) and then to put the formula in A1 of the first sheet, and then ungroup the sheets), but you will need to change Record1 (twice) to reflect the record number for each sheet in turn. Then the formula can be copied down as far as you need - until you get blanks.

The attached workbook illustrates this. I've coloured all the entries that I've made in a pale yellow background for clarity. All formulae will scale up to 50,000+ rows, but you need to ensure that the formulae are copied down sufficient rows. The formulae are also compatible with Excel 2003 (as you posted an .xls file), but if this is to be used only in later versions you could simplify the second formula to this:

=IFERROR(INDEX('Base Data'!B:B,MATCH("Record1_"&ROWS($A$1:$A1),'Base Data'!C:C,0)),"")

and save it as an .xlsx file.

Hope this helps.

Pete