Simple IF formula problem

[AlmostBeginner19]

I think this problem is just really simple but for some reason I couldn't just get what to do.

I have the following formula. My only issue is the highlighted part.
=IFERROR(IF(Column A="Supplier",IF(OtherSheet[Date Column 1]="","Not Received",INDEX(OtherSheet[Date Column 1],MATCH(1,INDEX((Column C=OtherSheet[Column C])*(OtherSheet[Column E]="Yes"),0,1),0))),"Not Applicable"),"Not Received")

That part alone is my problem. It is a date column which I enter manually whenever I need to make an entry. When the cellis blank, the formula returns either 0-Jan-1990, or "Not Received". The "Not Received" is what I expect it to return. The problem is, when I'm evaluating the formula, it gives a random number when the source cell is blank such as 43871 or 43963, etc. So it results in this example =IFERROR(IF(Column A="Supplier",IF(43871=0,...……..).

I hope someone could help me identify and resolve the issue.

 

[jasonb75]

Also it would be helpful if you point out which parentheses are misplaced, since like I said, I copied the formula as it is

Please ignore my comment about parenthesis, I was sure that I found some that were misplaced when I looked at it previously, I remember looking at it and not being able to follow what it was doing because of this. That said, I've just been over the formulas in post 1 and 7 (twice) and can't find any problems with the placement so I'm going to blame that on me posting with an early morning caffeine deficiency

As I pointed out, some of your descriptive references were a bit misleading, a better way to shorten the formula would have been to use A2, C2, $C$2:$C$10, C:C Sheet2!$C$2:$C$10, etc to give a better indication of what was being referred to.

Having gone over it again, I think that this might work for what you're trying to do, as before I'm having a little trouble following exactly what is needed but hopefully I have it somewhere close.

I've assumed that earliest date will be closest to the top of OtherSheet[Date Column 1], if it is not then the results might not be correct.

=IFERROR(IF(Column A="Supplier",IF(OtherSheet[Date Column 1]="","Not Received",IFERROR(AGGREGATE(15,6,OtherSheet[Date Column 1]/(Column C=OtherSheet[Column C])/(OtherSheet[Column E]="Yes"),1),"")),"Not Applicable"),"Not Received")