VLookup Multiple Data Rows

[alexdwsn]

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

 

[Domenic]

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,Sheet2!$A2),INDEX(Sheet1!$
A$2:$H$10,SMALL(IF(Sheet1!$A$2:$A$10=Sheet2!$A2,ROW(Sheet1!$A$2:$A$10)-RO
W(Sheet1!$A$2)+1),COLUMNS($B2:B2)),8),"")

Hope this helps!

 

[alexdwsn01]

Thanks, that's very helpful and works well but I've now been asked to d
something else with this data which means I now need to do the followin
and extract the whole row for matching records.

I need to return the whole row from Sheet1!A2:N16428 based on Sheet
column A values matching any of the values listed in Sheet2!A2:A2015
Sheet2!A2:A2015 contains values which may occur multiple times i
Sheet1!A2:A16428 and I need to extract all rows which match

Please help.

Many thank