Offset & Match Formula Shows Duplicates

[Joe Gieder]

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

 

[Frank Kabel]

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)))

 

[Joe Gieder]

Hi Frank,

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)))

 

[Joe Gieder]

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]

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))

 

[Joe Gieder]

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]

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

 

[Joe Gieder]

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]

Hi
have you entered my formulas as array formula?

 

[Joe Gieder]

I did.

Hi
have you entered my formulas as array formula?

 

[Frank Kabel]

Hi
if you like email me your non working sample sheet

 

[Joe Gieder]

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]

I will thank you. Were should I send it to?

Joe

Hi
if you like email me your non working sample sheet

 

[Frank Kabel]

frank[dot]kabel[at]freenet[dot]de

 

[Joe Gieder]

I will thank you. My email is (e-mail address removed) if you want to send your
address that way.

Joe