Formula help

[Jemma Atkinson]

Hi, the below formula looks at cell C9 then looks for the code in "Current Deliverables", if match found then it returns "IN DELIVERABLE" else "NOT DELIVERABLE"

I would like to add another condition, i need the formula to also look in cell F9 of the current worksheet, if the string in this cell reads "closed" then return "PORTFOLIO CLOSED" rather than "NOT IN DELIVERABLE"


=IF($C9="","",IF(ISERROR(MATCH($C9,'Current Deliverables'!$A:$A,0)),"NOT IN DELIVERABLE","IN DELIVERABLE"))

 

[shg]

maybe ...

=IF($C9="","",
IF(F9="Closed", "PORTFOLIO CLOSED",
IF(ISERROR(MATCH($C9,'Current Deliverables'!$A:$A,0)), "NOT ", "") & "IN DELIVERABLE"))

 

[Jemma Atkinson]

Why does this return "IN DELIVERBALE" when the string in F9 reads "Closed"

Excel Workbook
	C	D	E	F
6	Portfolio	Investment Manager	IM Rec Portfolio	Comments
9	ZMTCWC	Test	IN DELIVERABLE	Portfolio Closed
Checklist

 

[shg]

Looks like the string in F9 says "Portfolio Closed", not "Closed"

 

[Jemma Atkinson]

Looks like the string in F9 says "Portfolio Closed", not "Closed"

I need the formula look for the word "Closed" there could be other comments in the cell

 

[shg]

=IF($C9="","",
IF(ISNUMBER(SEARCH("closed", F9)), "PORTFOLIO CLOSED",
IF(ISERROR(MATCH($C9,'Current Deliverables'!$A:$A,0)), "NOT ", "") & "IN DELIVERABLE"))

 

[Jemma Atkinson]

Perfect, thanks