Question regarding formula

[Joe Gieder]

First, thank you in advance for your help and looking at message.

I use this array formula:
=IF(F37="","",OFFSET('Priced BOM'!$T$3,MATCH(F37,'Priced
BOM'!$AD$3:$AD$1270,0)-1,-9,1,1))
and it works fine with one exception. The formula is copied down many rows
and what it does is takes the total cost of a part (F37) and matches it to
$AD$3:$AD$1270. When the result is found it then copies the part number, the
problem is that if two different part numbers exist with the same cost it
will only show the first entry (I know this is how match works, finding the
first occurance and then proceeding to the next row). Is there a way to say
if the row above is the same value skip to the next value with the same cost?

Thanks in advance for the help.

Joe

 

[Domenic]

Maybe...

=IF(F37="","",OFFSET('Priced BOM'!$T$3,MATCH(2,1/('Priced
BOM'!$AD$3:$AD$1270=F37))-1,-9,1,1))

....which will match the last occurrence. Although, I'd use the
following formula instead...

=IF(F37<>"",INDEX('Priced BOM'!$K$3:$K$1270,MATCH(2,1/('Priced
BOM'!$AD$3:$AD$1270=F37))),"")

....because it excludes the OFFSET function which is volatile. Note that
both formulas need to be confirmed with CONTROL+SHIFT+ENTER, not just
ENTER.

Hope this helps!

 

[Biff]

Hi!

This seems backwards to me! But what do I know!

You lookup the price to get the part number? Why don't you lookup the part
number to get the price?

A couple of tidbits:

That formula does not need to be array entered.

it works fine with one exception

Then that means it DOESN'T work! <g>

A better, non-volatile formula to use:

=IF(F37="","",INDEX($K$3:$K$1270,MATCH(F37,$AD$3:$AD$1270,0)))

Is there a way to say if the row above is the same
value skip to the next value with the same cost?

If you have multiple instances of a price how do you know which part number
you want returned?

Are there any instances where 5 or 10 items may have the same price? How do
you know which corresponding part you want?

Biff

 

[Joe Gieder]

Hi Biff,

What I'm doing is listing the top 10 and 35 most expensive parts. I know
this can be done with autofilter and copying to the spreadsheet but I needfor
this to be formula driven.
The formula does work because I don't very often have the same price for
several different part numbers but when I do I get the first part number all
the time.
With the information I use many part numbers can have the same cost but I
want to be able to show each and every occurance, it causes confusion when
one part number shows up multiple time with the same dollar value.