Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.worksheet.functions
alexdwsn
 
Posts: n/a
Default 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   Report Post  
Posted to microsoft.public.excel.worksheet.functions
Domenic
 
Posts: n/a
Default 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   Report Post  
Posted to microsoft.public.excel.worksheet.functions
alexdwsn01
 
Posts: n/a
Default 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
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
Excel: How to choose data on two separate rows in the same column RicardoE Excel Worksheet Functions 2 February 24th 06 01:41 AM
Multiple if or multiple vlookup Robo Excel Worksheet Functions 4 November 14th 05 02:48 PM
VLOOKUP to return multiple rows Miss Marple Excel Worksheet Functions 2 September 19th 05 11:08 AM
Display rows of data - vlookup? Greg Excel Worksheet Functions 2 June 8th 05 01:51 PM
Select all data, multiple rows George Wilson Excel Discussion (Misc queries) 6 December 5th 04 09:16 PM


All times are GMT +1. The time now is 12:43 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"