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.
 

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.

gaz_chops

Well-known Member
Joined
Apr 29, 2003
Messages
6,468
Platform
  1. MacOS
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
Joined
Oct 30, 2019
Messages
11

ADVERTISEMENT

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
Joined
Apr 29, 2003
Messages
6,468
Platform
  1. MacOS
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
Joined
Oct 30, 2019
Messages
11

ADVERTISEMENT

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
Joined
Dec 30, 2008
Messages
12,666
Office Version
  1. 365
Platform
  1. Windows
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
Joined
Oct 30, 2019
Messages
11
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
Joined
Sep 9, 2015
Messages
89
Office Version
  1. 2010
Platform
  1. Windows
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.
 

Watch MrExcel Video

Forum statistics

Threads
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.
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
Top