Hi Frank,
Thank you for the formula. Maybe I did something wrong, when I copied it I
came out with the wrong result, the result I obtained was two rows below the
correct one and then when I copied the formula down I ended up with a #REF
error because I hit the end of the Priced BOM spreadsheet (there should be 35
results). What could I have done wrong, I copied it exactly like you wrote
it? The cell I want to use as the reference is F37 which is the cost and it
looks in 'Priced BOM' AD3:AD164 I'm trying to retrieve the part number which
is in 'Priced BOM' K3:K164. I'm sure there's something I didn't follow right
could you please help.

Thanks
Joe

"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