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!

In article <[email protected]>,
alexdwsn <[email protected]>
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

[alexdwsn01]

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