On Apr 5, 9:06 pm, Ron Coderre
wrote:
DRAT! My fingers not typing what I'm thinking!

Formula 1 should be:
E1:
=IF(COUNTIF($A$2:$A$5,$D$1)=ROWS($E$1:$E1),INDEX( $B$2:$B$5,SMALL(INDEX(($A$2:$A$5=$D$1)*ROW($A$2:$A $5)+($A$2:$A$5<$D$1)*10^99,0),ROWS($E$1:$E1))),"" )

Formula 2 (the array formula) should be:
E1:
=IF(COUNTIF($A$2:$A$5,$D$1)=ROWS($E$1:$E1),INDEX( $B$2:$B$5,SMALL(IF($A$2:$A$5=$D$1,ROW($A$2:$A$5)), ROWS($E$1:$E1))),"")

Does that help?
***********
Regards,
Ron

XL2002, WinXP

"Ron Coderre" wrote:
With
Col_A containing PartNums, beginning in Cell A2
and
Col_B containing WorkOrders, beginning in cell B2

Then
D1: (a part number)

This regular formula returns the WorkOrders associated with that PartNum
E1:
=IF(COUNTIF($A$2:$A$5,$D$1)=ROWS($E$1:$E1),SMALL( INDEX(($A$2:$A$5=$D$1)*ROW($A$2:$A$5)+($A$2:$A$5< $D$1)*10^99,0),ROWS($E$1:$E1)),"")

Adjust range references to suit your situation.
Copy that formula down as far as you need.

Alternatively, you could use this ARRAY FORMULA
(committed with [ctrl]+[shift]+[enter].....instead of just [enter])
E1:
=IF(COUNTIF($A$2:$A$5,$D$1)=ROWS($E$1:$E1),SMALL( IF($A$2:$A$5=$D$1,ROW($A$2:$A$5)),ROWS($E$1:$E1)), "")

Is that something you can work with?
***********
Regards,
Ron

XL2002, WinXP

"deen" wrote:

Is there any way to lookupmultipleanswers to a lookup formula.
Normally,
Excel will stop at the first match it finds and return that as the
answer
when, in fact, there may be more than one answer. I want to find the
other
matches as well and list them separately.

Here's an example.

Part No. Work Order
123 6000
123 6010
130 7000
135 7050

Part No. 123 has 2 (production) work orders associated with it: 6000
and
6010. I want to do a lookup of Part No. 123 and find ALL the work
orders
associated with it (listed separately, of course). Is there any way
to do
I want Data Excel sheet2?

Pls help Me

Pls kindly sugess other formula is not properly