Trouble with Array multiple return formula
 

Hi-
I've been trying to get multiple returns on an array formula with no
success. I still get a single return. I've looked through the other posts and
I think the formula below is the answer to my problem. I got it to work once
on a sample and then couldn't get the sample to work again after I retyped
the formula. Now it only returns a single result.

Essentially what I'm doing is comparing avg pricing to customer pricing. If
a customer has a price on a certain product, I want to see it. My set up is
(A) Code, (B) Avg. Co. Price, (C) Codes (these match the previous code column
but are showing wich customers have them), (D) Customer, (E) Customer Price.

Any ideas? Thanks.

=INDEX(D2:D219,SMALL(IF((C2:C219=A156),ROW(C2:C219 )),ROW()))

Trouble with Array multiple return formula
 

That looks like an array formula, which means you need array-enter it
(Ctrl-Shift-Enter).

It also looks to me tat it should be

=INDEX(D2:D219,SMALL(IF((C2:C219=A1),ROW(C2:C219)-ROW(C2)+1),ROW(A1)))

--
__________________________________
HTH

Bob

"Kate" wrote in message
...
Hi-
I've been trying to get multiple returns on an array formula with no
success. I still get a single return. I've looked through the other posts
and
I think the formula below is the answer to my problem. I got it to work
once
on a sample and then couldn't get the sample to work again after I retyped
the formula. Now it only returns a single result.

Essentially what I'm doing is comparing avg pricing to customer pricing.
If
a customer has a price on a certain product, I want to see it. My set up
is
(A) Code, (B) Avg. Co. Price, (C) Codes (these match the previous code
column
but are showing wich customers have them), (D) Customer, (E) Customer
Price.

Any ideas? Thanks.

=INDEX(D2:D219,SMALL(IF((C2:C219=A156),ROW(C2:C219 )),ROW()))

Trouble with Array multiple return formula
 

=INDEX(D2:D219,SMALL(IF((C2:C219=A156),ROW(C2:C21 9)),ROW()))

What cell are you entering this formula in?

Array entered** :

=INDEX(D$2:D$219,SMALL(IF(C$2:C$219=A$156,ROW(C$2: C$219)),ROWS(A$1:A1))-ROW(C$2)+1)

** array formulas need to be entered using the key combination of
CTRL,SHIFT,ENTER (not just ENTER). Hold down both the CTRL key and the SHIFT
key then hit ENTER.

Replace A$1:A1 with the cell you're entering the formula in. For example, if
you're entering the formula in cell AX22 then:

=INDEX(D$2:D$219,SMALL(IF(C$2:C$219=A$156,ROW(C$2: C$219)),ROWS(AX$22:AX22))-ROW(C$2)+1)

Copy down as needed.

--
Biff
Microsoft Excel MVP


"Kate" wrote in message
...
Hi-
I've been trying to get multiple returns on an array formula with no
success. I still get a single return. I've looked through the other posts
and
I think the formula below is the answer to my problem. I got it to work
once
on a sample and then couldn't get the sample to work again after I retyped
the formula. Now it only returns a single result.

Essentially what I'm doing is comparing avg pricing to customer pricing.
If
a customer has a price on a certain product, I want to see it. My set up
is
(A) Code, (B) Avg. Co. Price, (C) Codes (these match the previous code
column
but are showing wich customers have them), (D) Customer, (E) Customer
Price.

Any ideas? Thanks.

=INDEX(D2:D219,SMALL(IF((C2:C219=A156),ROW(C2:C219 )),ROW()))

Trouble with Array multiple return formula
 

Thanks! This works for me. After I put your formula in, I realized I needed
to change my =A156 to a constant and then it worked perfectly. Thanks so much
for your help. You saved me a lot of frustration.

"T. Valko" wrote:

=INDEX(D2:D219,SMALL(IF((C2:C219=A156),ROW(C2:C21 9)),ROW()))

What cell are you entering this formula in?

Array entered** :

=INDEX(D$2:D$219,SMALL(IF(C$2:C$219=A$156,ROW(C$2: C$219)),ROWS(A$1:A1))-ROW(C$2)+1)

** array formulas need to be entered using the key combination of
CTRL,SHIFT,ENTER (not just ENTER). Hold down both the CTRL key and the SHIFT
key then hit ENTER.

Replace A$1:A1 with the cell you're entering the formula in. For example, if
you're entering the formula in cell AX22 then:

=INDEX(D$2:D$219,SMALL(IF(C$2:C$219=A$156,ROW(C$2: C$219)),ROWS(AX$22:AX22))-ROW(C$2)+1)

Copy down as needed.

--
Biff
Microsoft Excel MVP


"Kate" wrote in message
...
Hi-
I've been trying to get multiple returns on an array formula with no
success. I still get a single return. I've looked through the other posts
and
I think the formula below is the answer to my problem. I got it to work
once
on a sample and then couldn't get the sample to work again after I retyped
the formula. Now it only returns a single result.

Essentially what I'm doing is comparing avg pricing to customer pricing.
If
a customer has a price on a certain product, I want to see it. My set up
is
(A) Code, (B) Avg. Co. Price, (C) Codes (these match the previous code
column
but are showing wich customers have them), (D) Customer, (E) Customer
Price.

Any ideas? Thanks.

=INDEX(D2:D219,SMALL(IF((C2:C219=A156),ROW(C2:C219 )),ROW()))

Trouble with Array multiple return formula
 

Good deal. Thanks for the feedback!

--
Biff
Microsoft Excel MVP


"Kate" wrote in message
...
Thanks! This works for me. After I put your formula in, I realized I
needed
to change my =A156 to a constant and then it worked perfectly. Thanks so
much
for your help. You saved me a lot of frustration.

"T. Valko" wrote:

=INDEX(D2:D219,SMALL(IF((C2:C219=A156),ROW(C2:C21 9)),ROW()))

What cell are you entering this formula in?

Array entered** :

=INDEX(D$2:D$219,SMALL(IF(C$2:C$219=A$156,ROW(C$2: C$219)),ROWS(A$1:A1))-ROW(C$2)+1)

** array formulas need to be entered using the key combination of
CTRL,SHIFT,ENTER (not just ENTER). Hold down both the CTRL key and the
SHIFT
key then hit ENTER.

Replace A$1:A1 with the cell you're entering the formula in. For example,
if
you're entering the formula in cell AX22 then:

=INDEX(D$2:D$219,SMALL(IF(C$2:C$219=A$156,ROW(C$2: C$219)),ROWS(AX$22:AX22))-ROW(C$2)+1)

Copy down as needed.

--
Biff
Microsoft Excel MVP


"Kate" wrote in message
...
Hi-
I've been trying to get multiple returns on an array formula with no
success. I still get a single return. I've looked through the other
posts
and
I think the formula below is the answer to my problem. I got it to work
once
on a sample and then couldn't get the sample to work again after I
retyped
the formula. Now it only returns a single result.

Essentially what I'm doing is comparing avg pricing to customer
pricing.
If
a customer has a price on a certain product, I want to see it. My set
up
is
(A) Code, (B) Avg. Co. Price, (C) Codes (these match the previous code
column
but are showing wich customers have them), (D) Customer, (E) Customer
Price.

Any ideas? Thanks.

=INDEX(D2:D219,SMALL(IF((C2:C219=A156),ROW(C2:C219 )),ROW()))