J
Joe Gieder
I use this array formula to identify what price is used for a part number:
=IF(ISTEXT($R4),,IF($H4="Yes",IF(ISNA(VLOOKUP(K4,'MSP
Listing'!$A$6:$D$2260,4,0)),VLOOKUP(K4,'MSP
Listing'!$B$6:$D$2260,3,0)),(IF(ISNA(INDEX(UnitCost,MATCH($K4&MIN(IF((PN=$K4)*(ExtendCost<>0)*(Quoted<>"Yes")*(Updated<>"Yes"),ExtendCost)),PN&ExtendCost,),0)),0,INDEX(UnitCost,MATCH($K4&MIN(IF((PN=$K4)*(ExtendCost<>0)*(Quoted<>"Yes")*(Updated<>"Yes"),ExtendCost)),PN&ExtendCost,),0)))))
The formula works if H4 is not "Yes", it returns the correct value in S4.
The formula works if H4 is "Yes" and the next IF statement evaluates to
False.
The formula does not work if H4 is "Yes" and cell S4 does not result in N/A,
it returns FALSE as the value and I need it to return the evaluated cell
value.
I'm sorry for the legthy topic and I hope I was fairly clear on the problem.
If any additional clarrification is needed I'll do what I can.
TIA for your help
Joe
=IF(ISTEXT($R4),,IF($H4="Yes",IF(ISNA(VLOOKUP(K4,'MSP
Listing'!$A$6:$D$2260,4,0)),VLOOKUP(K4,'MSP
Listing'!$B$6:$D$2260,3,0)),(IF(ISNA(INDEX(UnitCost,MATCH($K4&MIN(IF((PN=$K4)*(ExtendCost<>0)*(Quoted<>"Yes")*(Updated<>"Yes"),ExtendCost)),PN&ExtendCost,),0)),0,INDEX(UnitCost,MATCH($K4&MIN(IF((PN=$K4)*(ExtendCost<>0)*(Quoted<>"Yes")*(Updated<>"Yes"),ExtendCost)),PN&ExtendCost,),0)))))
The formula works if H4 is not "Yes", it returns the correct value in S4.
The formula works if H4 is "Yes" and the next IF statement evaluates to
False.
The formula does not work if H4 is "Yes" and cell S4 does not result in N/A,
it returns FALSE as the value and I need it to return the evaluated cell
value.
I'm sorry for the legthy topic and I hope I was fairly clear on the problem.
If any additional clarrification is needed I'll do what I can.
TIA for your help
Joe