VLOOKUP to return multiple rows

M

Miss Marple

I have produced a workbook with a main sheet which summarises other
information from spreadsheets in the same workbook. I have used VLOOKUP and
this is fine when there is only one row of data to summarize from each
spreadsheet. Can anyone help me with a suggestion to automatically bring
through to the main sheet varying number of rows where my "primary key" could
be in between one and twenty rows.

Thank you
 
A

Arvi Laanemets

Hi

Find my response to topic, started by Sr. Vice Deli at 26.07.2005 21:47 "How
Do I organize sheets and clients by town?" (here in same NG, or search in
Google when you can't it download anymore. There are detailed instructions
how to do what you want.
 
M

Max

Just another play (non-array) ..

Link to demo file with the implemented construct at:
http://www.savefile.com/files/1001151
File: VLOOKUP to return multiple rows_MissMarple_wksht.xls

Assume the source data is in Sheet2 & Sheet3,
cols A & B, data from row2 down
(Identical structure)

In Sheet2
Key Field1
1113 90
1111 80
1112 34
etc

In Sheet3
Key Field1
1111 17
1113 66
1113 83
etc

In both Sheet2 and Sheet3
--------------
Put in E2:
=IF(A2="","",IF(A2=Sheet1!$A$1,ROW(),""))
Copy down to say E20, to cover the max expected data range
(Leave E1 empty)

In Sheet1 (the summary sheet)
-------------
Input the key in A1, e.g.: 1111
List in A3:B3, the sheetnames: Sheet2, Sheet3

Put in say, A4:
=IF(ISERROR(SMALL(INDIRECT("'"&A$3&"'!E:E"),
ROWS($A$1:A1))),"",INDEX(INDIRECT("'"&A$3&"'!B:B"),
MATCH(SMALL(INDIRECT("'"&A$3&"'!E:E"),
ROWS($A$1:A1)),INDIRECT("'"&A$3&"'!E:E"),0)))

Copy A4 across to B4, fill down to B22
(cover the same range size as was done in col E in Sheet2 / 3)

Field1 values corresponding to the key input in A1
will be extracted in cols A & B from each sheet
 
Top