An Array Lookup Formula

[Guest]

We need to write array formulae that return all occurrances of a lookup value
in a lookup array instead of just the first occurrance. For example:

Col. A
Apples
Apples
Pears
Oranges
Apples
Oranges
Plums
Pears

Col. B
2 bushels
4 bushels
1 bushel
2 bushels
3 bushels
2 bushels
5 bushels
3 bushels

We want array formulae in columns D & E to pull all the pears and their
corresponding quantities:

Col. D
Pears
Pears

Col. E
1 bushel
3 bushels

Any suggestions??
Thanks in advance!

 

[Guest]

Why not just filter on Pears?

 

[Guest]

Hi,
Unless there is a reason not to sort on column A, I think Jim has athe right
idea. Is there a reason you can not sort this way, mayme the order of Colum A
is important and needs to be preserved?
Thanks,

 

[Guest]

Ordinarily, filtering would work fine for a manual process. We are trying to
select multiple data sets from the same list data and return the answers to
multiple locations, in an automated fashion. Also, the sheet on which the
data list resides includes multiple combo boxes that get messed up after a
filter is run, so we need another solution aside from "auto filter" to
extract the necessary data.

 

[Guest]

Hi,
This creates a copy of the sheet and sorts it on Col A, so it does not
disturbe your original data. Hope it helps.

Sub Macro1()
Range("A1").Select
Selection.CurrentRegion.Select
ThisSheet = ActiveSheet.Name
Sheets(ThisSheet).Select
Sheets(ThisSheet).Copy Before:=Sheets(1)
Selection.Sort Key1:=Range("A1"), Order1:=xlAscending, Header:=xlGuess, _
OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _
DataOption1:=xlSortNormal
End Sub

Thanks,