Home |
Search |
Today's Posts |
#1
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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 |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Have Vlookup return a Value of 0 instead of #N/A | Excel Worksheet Functions | |||
Using a Vlookup to return values in a data list? | Excel Worksheet Functions | |||
Multiple rows of data on a single axis (charting) | Charts and Charting in Excel | |||
Lookup values in a list and return multiple rows of data | Excel Worksheet Functions | |||
Can vlookup return multiple matches in a single cell? | Excel Discussion (Misc queries) |