I did. However; I get a #NUM! error unless I put a $ in front of the first 1
in ROW(1:1)-2 and then the formul goes $1:1
$1:2
and so on.

"Joe Gieder" wrote:

The formulas I have tried are as entered starting in C37:

=IF(F37="","",OFFSET('Priced BOM'!$AD$3,MATCH(F37,'Priced
BOM'!$AD$3:$AD$164,0)-1,-19,1,1))

C37 F37 C37 Result SB
B32281006-102 $182,732.82 B32281006-102 correct
B32281006-101 $182,732.37 B32281006-101 correct
B32281006-103 $56,851.23 B32281006-103 correct
B32281006-104 $56,391.69 B32281006-104 correct
B32281006-109 $29,420.28 B32281006-109 correct
B32281006-109 $29,420.28 B32281006-110 wrong
368659-3 $28,218.75 368659-3 correct
368659-3 $28,218.75 368659-4 wrong
363576-6 $23,786.97 363576-6 correct
391066-1 $15,014.70 391066-1 correct
4085962-1 $11,669.16 4085962-1 correct
4085962-1 $11,669.16 4085962-2 wrong
and others..

And I used your formula:
=IF(F37="","",INDEX('Priced BOM'!$K$3:$K$164,SMALL(IF(F37='Priced
BOM'!$AD$3:$AD$164,ROW('Priced BOM'!$AD$3:$AD$164)),ROW(1:1))-2))
And received the same results as above.

Thank you and Best Regards
Joe



"Frank Kabel" wrote:

Hi
what is the exact formula you have used and what are your result

--
Regards
Frank Kabel
Frankfurt, Germany

"Joe Gieder" schrieb im
Newsbeitrag ...
I copied and pasted and I get different part numbers (the same
results as
with my first formula) but I still get duplicate part numbers if the
cost is
the same for two different parts. Sorry for the trouble.

Thanks
Joe

"Frank Kabel" wrote:

Hi
yes, my fault. Make this:
=IF(F37="","",INDEX('Priced BOM'!$K$3:$K$164,SMALL(IF(F37='Priced
BOM'!$AD$3:$AD$164,ROW('Priced BOM'!$AD$3:$AD$164)),ROW(1:1))-2))

--
Regards
Frank Kabel
Frankfurt, Germany

"Joe Gieder" schrieb im
Newsbeitrag
...
Hi Frank,


"Frank Kabel" wrote:

Hi
first: no need to array enter this formula at all. Should do as
non-aaray formula
One way to get multiple returns is the following array formula:

=IF(F37="","",INDEX('Priced
BOM'!$K$3:$K$164,SMALL(IF(F37='Priced
BOM'!$AD$3:$AD$164,ROW('Priced BOM'!$AD$3:$AD$164)),ROW(1:1))))

and copy this formula down.

Note: not completely robust due to the usage of ROW(1:1). If
you
for
example enter this formula in row 37 you could change the
formula
to:
=IF(F37="","",INDEX('Priced
BOM'!$K$3:$K$164,SMALL(IF(F37='Priced
BOM'!$AD$3:$AD$164,ROW('Priced
BOM'!$AD$3:$AD$164)),ROW()-ROW($F$37)+1)))

--
Regards
Frank Kabel
Frankfurt, Germany

"Joe Gieder" schrieb im
Newsbeitrag
...
I use this array formula {=IF(F37="","",OFFSET('Priced
BOM'!$AD$3,MATCH(F37,'Priced
BOM'!$AD$3:$AD$164,0)-1,-19,1,1))}
and
what it
does is find a part number (-19) baseed on the cost of an
item (F
&
AD). If
the cost of two or more items is the same it always lists the
first
part. Is
there a way to to have this formula check to see if the part
is
on
the row
above and if so have it list the next part that matches the
cost?

Thank you for your help and Happy Holidays
Joe