An Array Lookup Formula

[SueDot]

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

[Jim Thomlinson]

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

[David]

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

[SueDot]

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