Trouble with Array multiple return formula

[Kate]

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(D2219,SMALL(IF((C2:C219=A156),ROW(C2:C219)),ROW()))

 

[Bob Phillips]

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(D2219,SMALL(IF((C2:C219=A1),ROW(C2:C219)-ROW(C2)+1),ROW(A1)))

 

[T. Valko]

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

What cell are you entering this formula in?

Array entered** :

=INDEX(D$2$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$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.

 

[Kate]

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]

Good deal. Thanks for the feedback!