Formula Returning "FALSE"
 

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(UnitCos t,MATCH($K4&MIN(IF((PN=$K4)*(ExtendCost<0)*(Quote d<"Yes")*(Updated<"Yes"),ExtendCost)),PN&ExtendC ost,),0)),0,INDEX(UnitCost,MATCH($K4&MIN(IF((PN=$K 4)*(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

Formula Returning "FALSE"
 

=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(UnitCos
t,MATCH($K4&MIN(IF((PN=$K4)*(ExtendCost<0)*(Quote
d<"Yes")*(Updated<"Yes"),ExtendCost)),PN&Extend C
ost,),0)),0,INDEX(UnitCost,MATCH($K4&MIN(IF((PN=$K
4)*(ExtendCost<0)*(Quoted<"Yes")*(Updated<"Yes"
),ExtendCost)),PN&ExtendCost,),0)))))

If you copy pasted correctly, the space on "UnitCos t" above might be
causing the problem.


--
renegan
------------------------------------------------------------------------
renegan's Profile: http://www.excelforum.com/member.php...o&userid=10450
View this thread: http://www.excelforum.com/showthread...hreadid=538206

Formula Returning "FALSE"
 

I looked at he formula on the spreadsheet and it doesn't have the space, it
must have been copying it to here that it put the space.
Thank you for the input.

Joe


"renegan" wrote:


=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(UnitCos
t,MATCH($K4&MIN(IF((PN=$K4)*(ExtendCost<0)*(Quote
d<"Yes")*(Updated<"Yes"),ExtendCost)),PN&Extend C
ost,),0)),0,INDEX(UnitCost,MATCH($K4&MIN(IF((PN=$K
4)*(ExtendCost<0)*(Quoted<"Yes")*(Updated<"Yes"
),ExtendCost)),PN&ExtendCost,),0)))))

If you copy pasted correctly, the space on "UnitCos t" above might be
causing the problem.


--
renegan
------------------------------------------------------------------------
renegan's Profile: http://www.excelforum.com/member.php...o&userid=10450
View this thread: http://www.excelforum.com/showthread...hreadid=538206