Simple IF formula problem

AlmostBeginner19

New Member
Joined
Oct 30, 2019
Messages
11
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.
 
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 :oops:

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")
 
Upvote 0

Excel Facts

Enter current date or time
Ctrl+: enters current time. Ctrl+; enters current date. Use Ctrl+: Ctrl+; Enter for current date & time.

Forum statistics

Threads
1,213,536
Messages
6,114,208
Members
448,554
Latest member
Gleisner2

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top