# Simple IF formula problem

#### AlmostBeginner19

##### New Member
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.

### Excel Facts

VLOOKUP to Left?
Use =VLOOKUP(A2,CHOOSE({1,2},\$Z\$1:\$Z\$99,\$Y\$1:\$Y\$99),2,False) to lookup Y values to left of Z values.

#### AlmostBeginner19

##### New Member

That made it worse actually. Now even the ones that should have a date are showing "Not Received".
Thanks for trying though.

#### gaz_chops

##### Well-known Member
If Column 1 actually is a date it should work, a date is simply a number that will always be >=1, if it is blank or zero it is <1.

What is in Col 1?

#### AlmostBeginner19

##### New Member

If Column 1 actually is a date it should work, a date is simply a number that will always be >=1, if it is blank or zero it is <1.

What is in Col 1?

Just dates. When there's no date, it's just blank. I just now noticed that it's not only the date column. It's other columns also.
And yeah, I already tried wrapping the formula in an ISBLANK formula instead of using ="", but I'm getting the same result.

#### gaz_chops

##### Well-known Member
How are the dates entered?
if you click on a cell with a date in, then click the comma icon, what is displayed?

#### AlmostBeginner19

##### New Member

How are the dates entered?
if you click on a cell with a date in, then click the comma icon, what is displayed?

It's on a date format (e.g. 12-Jul-2020). When I click comma style, it turns into a number value (e.g. 44,024.00).

I think I got it now, though I haven't tried it with all of the columns yet because there are 10+ columns, but so far for the rest of the columns, it's working. I added the part in BOLD on the formula below.

=IFERROR(IF(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")="","",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")

#### jasonb75

##### Well-known Member
Looking at your formula, I would say that there are a lot of points where it could fail.

I've tried to break it down bit you have misplaced parentheses and a number of range descriptions which are unclear. Does 'Column A' mean entire column, or just the cell in the same row as the formula? From what I can see (given the limited information that we have) it is highly likely that the formula will not be comparing data on the current sheet to the 'other sheet' correctly.

#### AlmostBeginner19

##### New Member
Looking at your formula, I would say that there are a lot of points where it could fail.

I've tried to break it down bit you have misplaced parentheses and a number of range descriptions which are unclear. Does 'Column A' mean entire column, or just the cell in the same row as the formula? From what I can see (given the limited information that we have) it is highly likely that the formula will not be comparing data on the current sheet to the 'other sheet' correctly.

I copied the formula exactly as I've used it, except that I changed the column names into simpler forms such as "Column A". This is because the column names we use are a bit lengthy due to some requirements so I minimized the column names to avoid confusion. Columns I mentioned are just columns that cover a range, not the entire worksheet column.

Also it would be helpful if you point out which parentheses are misplaced, since like I said, I copied the formula as it is, except that I changed the column names. I just hope that people would be more "this is where you could improve" instead of "you wrote it wrong".

#### ShuStar

##### Board Regular
Maybe worth checking if the cell is actually blank. Sometimes there are white spaces which impact the forumal. You can use TRIM to remove those cells that appear blank but actually contain a space in it.

Replies
6
Views
113
Replies
8
Views
98
Replies
3
Views
128
Replies
0
Views
62
Replies
31
Views
316

1,130,119
Messages
5,640,218
Members
417,131
Latest member
Seanr19871

### 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.

### Which adblocker are you using?

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

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