Nesting Maybe?

[Maximillon]

I have wore google out past few days trying to figure this out.

I am currently using vlookup to pull cost from another workbook.
=VLOOKUP(C12,'[Quote Database.xlsx]Cost'!$A$2:$C$2013,2, FALSE)

However sometimes there will be 2 different costs for the same part number. If that is the case I just want the cost cell to say Void. So we can manually look up the cost.

2nd way maybe?
I also use this to pull over Yen cost in a different cell (I12)
=VLOOKUP(C12,'[Quote Database.xlsx]Cost'!$A$2:$C$2013,3, FALSE)
=IF D12 is "CT" or "ST" AND I12 is greater then 0 VOID
this is saying hey this part is made in CT or ST (US) but there is a Japan cost (meaning there is multiple costs)

Possibly confusing, I can message a sample of the workbook if need be.

Thanks for any help!

 

[steve the fish]

Hi. Does this work?

=IF(COUNTIF('[Quote Database.xlsx]Cost'!$A$2:$A$2013,C12)>1,"Void",VLOOKUP(C12,'[Quote Database.xlsx]Cost'!$A$2:$C$2013,2,FALSE))

 

[Maximillon]

Steve,

That does work, however not for this case, as most parts have duplicates, but all have the same cost (which im ok with)

The ones that are issues are where i have 2 costs that are different (manufactured part vs. purchased part)
For example
A B C
Part 1| $1.00 | ¥.099
Part 1| $.05

Where the first line is if we purchased the part, the 2nd line is if we manufactured it.
In this case the cost will need to be investigated and see if we make it or buy it so "void" works
I would manually go through the database and correct all duplicates, but i will need to update it once a month, i dont want those problems.

 

[steve the fish]

So you want void if the dollar value in B is different?

 

[Maximillon]

Correct

 

[steve the fish]

Try this then:

=IF(VLOOKUP(C12,'[Quote Database.xlsx]Cost'!$A$2:$B$2013,2,0)=AVERAGEIF('[Quote Database.xlsx]Cost'!$A$2:$A$2013,C12,'[Quote Database.xlsx]Cost'!$B$2:$B$2013),VLOOKUP(C12,'[Quote Database.xlsx]Cost'!$A$2:$B$2013,2,0),"Void")

 

[Maximillon]

That worked for this scenario, but not the second.
Part 1|$1
Part 1|$1
Part 1|$.05

Not this one, it returned $.75
Part 2|$.75
Part 2|$1
Part 2|$1
Part 2|$1

 

[steve the fish]

That can only mean that the 1 dollar values are not numbers but text. Test them with =ISNUMBER(A1). Replace A1 with the cell one of the $1 is in.

Actually it could also mean that Part 2 for the $1 amounts was not really equal to Part 2. It may have a trailing space for example. Check that Part 2 really does equal Part 2.

 

[Maximillon]

Went to have a co worker test this and realized if the "Database" isn't open it wont pull over the data. So decided to merge the 2 work books. Now the formula wont work.

Changed '[Quote Database.xlsx]Cost'!$A$2:$B$2013 to Cost!A1:C2007,C12

I assume its something little I'm missing?

=IF(VLOOKUP(C12,Cost!A1:C2007,2,0)=AVERAGEIF(Cost!A1:C2007,C12,Cost!A1:C2007),VLOOKUP(C12,Cost!A1:C2007,2,0),"Void")

 

[steve the fish]

It's the averageif. You dont say A:C, C12, A:C. You say A:A, C12, C:C most likely. Only one column for each not all three columns.