Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
VLookup Multiple Data Rows
Sheet A contains a large (16000 rows, 8 columns) array of data and Sheet B a single column which lists certain text values also found in column 1 of Sheet A. The values listed in Sheet B can appear multiple times in the array in Sheet A. I want to return from Sheet A the column 8 value (numeric) corresponding to each occurance of the Sheet B values in column 1 of Sheet A The limit of my knowledge in VLOOKUP, but that of course will only return one Sheet A value from each occurance of the values listed in Sheet B. I hope that makes sense, please can somebody help? Many thanks -- alexdwsn ------------------------------------------------------------------------ alexdwsn's Profile: http://www.excelforum.com/member.php...o&userid=35241 View this thread: http://www.excelforum.com/showthread...hreadid=550153 |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
VLookup Multiple Data Rows
Let's assume that Sheet1!A2:H10 contains the source data, and
Sheet2!A2:A5 contains the text values for which to search, try the following formula which needs to be confirmed with CONTROL+SHIFT+ENTER, not just ENTER... Sheet2!B2, copied across and down: =IF(COLUMNS($B2:B2)<=COUNTIF(Sheet1!$A$2:$A$10,She et2!$A2),INDEX(Sheet1!$ A$2:$H$10,SMALL(IF(Sheet1!$A$2:$A$10=Sheet2!$A2,RO W(Sheet1!$A$2:$A$10)-RO W(Sheet1!$A$2)+1),COLUMNS($B2:B2)),8),"") Hope this helps! In article , alexdwsn wrote: Sheet A contains a large (16000 rows, 8 columns) array of data and Sheet B a single column which lists certain text values also found in column 1 of Sheet A. The values listed in Sheet B can appear multiple times in the array in Sheet A. I want to return from Sheet A the column 8 value (numeric) corresponding to each occurance of the Sheet B values in column 1 of Sheet A The limit of my knowledge in VLOOKUP, but that of course will only return one Sheet A value from each occurance of the values listed in Sheet B. I hope that makes sense, please can somebody help? Many thanks |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
VLookup Multiple Data Rows
Thanks, that's very helpful and works well but I've now been asked to do something else with this data which means I now need to do the following and extract the whole row for matching records. I need to return the whole row from Sheet1!A2:N16428 based on Sheet1 column A values matching any of the values listed in Sheet2!A2:A2015. Sheet2!A2:A2015 contains values which may occur multiple times in Sheet1!A2:A16428 and I need to extract all rows which match Please help. Many thanks -- alexdwsn01 ------------------------------------------------------------------------ alexdwsn01's Profile: http://www.excelforum.com/member.php...o&userid=35250 View this thread: http://www.excelforum.com/showthread...hreadid=550153 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Excel: How to choose data on two separate rows in the same column | Excel Worksheet Functions | |||
Multiple if or multiple vlookup | Excel Worksheet Functions | |||
VLOOKUP to return multiple rows | Excel Worksheet Functions | |||
Display rows of data - vlookup? | Excel Worksheet Functions | |||
Select all data, multiple rows | Excel Discussion (Misc queries) |