An Array Lookup Formula
 

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!
--
Susan

An Array Lookup Formula
 

Why not just filter on Pears?

"SueDot" wrote:

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!
--
Susan

An Array Lookup Formula
 

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,

"SueDot" wrote:

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!
--
Susan

An Array Lookup Formula
 

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.

"Jim Thomlinson" wrote:

Why not just filter on Pears?

"SueDot" wrote:

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!
--
Susan

An Array Lookup Formula
 

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 Befo=Sheets(1)
Selection.Sort Key1:=Range("A1"), Order1:=xlAscending, Header:=xlGuess, _
OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _
DataOption1:=xlSortNormal
End Sub

Thanks,


"SueDot" wrote:

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.

"Jim Thomlinson" wrote:

Why not just filter on Pears?

"SueDot" wrote:

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!
--
Susan