a problem with If/Vlookup statement

[William Poh Ben]

Hi,

I always encountered this problem in vlookup such that when there is a
duplicate PartNumber in the lookup column A (but with different unit
costs in column C), it always vlookup for the first unit cost it comes
across.
I demonstrate an example below.

What formula to use if Vlookup comes across duplicate PartNumbers and
let it returns the maximum unit cost ? (eg. vlookup partNumber 25557A
to return the max unit cost $0.82 in stead of the first value $0.45)

Thanks so much in advance !


ColumnA ColumnB ColumnC

PartNumber Use-in-Product Unit Cost$
24555A Speaker 0.55
25557A Keyboard 0.45
25557A Mouse 0.82
25557A Casing 0.67
25111C Modem 1.10

 

[Jim]

It would seem that your PartNumbers would have to be unique in order to
return the correct value. I have not seen a table set up where you have a
Part Number with a price of *whatever* and somewhere else in the table the
same Part Number with a different price. If you are discounting the price
for quantity/customer loyalty etc., you should set up a discount table.

 

[Dave Peterson]

I, too, find it kind of weird that unit cost changes for the same part number,
but this formula worked ok for me:

=IF(ISERROR(MATCH(A1,Sheet1!$A$2:$A$6,0)),"Missing",
MAX(IF(Sheet1!$A$2:$A$6=A1,Sheet1!$C$2:$C$6)))

(one cell)
This is an array formula. Hit ctrl-shift-enter instead of enter. If you do it
correctly, excel will wrap curly brackets {} around your formula. (don't type
them yourself.)

(I put the table on sheet1 and my formula/data were on a different sheet.)

If it's ok to return 0 as your value when there isn't a match, you could use
this:

=MAX(IF(Sheet1!$A$2:$A$6=A1,Sheet1!$C$2:$C$6))
(still hit ctrl-shift-enter, though.)