Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Miss Marple
 
Posts: n/a
Default VLOOKUP to return multiple rows

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
  #2   Report Post  
Arvi Laanemets
 
Posts: n/a
Default

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.


--
Arvi Laanemets
( My real mail address: arvil<attarkon.ee )



"Miss Marple" wrote in message
...
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



  #3   Report Post  
Max
 
Posts: n/a
Default

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
--
Rgds
Max
xl 97
---
Singapore, GMT+8
xdemechanik
http://savefile.com/projects/236895
--
"Miss Marple" wrote in message
...
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



Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
Have Vlookup return a Value of 0 instead of #N/A Mr Mike Excel Worksheet Functions 4 May 25th 05 04:51 PM
Using a Vlookup to return values in a data list? rtjeter Excel Worksheet Functions 2 April 26th 05 05:56 AM
Multiple rows of data on a single axis (charting) ramseysgirl Charts and Charting in Excel 8 December 29th 04 07:00 PM
Lookup values in a list and return multiple rows of data Amanda L Excel Worksheet Functions 2 December 2nd 04 05:48 PM
Can vlookup return multiple matches in a single cell? cchristensen Excel Discussion (Misc queries) 3 December 2nd 04 02:56 AM


All times are GMT +1. The time now is 09:20 PM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"