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

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

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