00/Jan/1900

stoothom

Board Regular
Joined
Feb 16, 2017
Messages
62
Hi,

I've a formula I'm using to look across a range and pick all the values that match (plenty of googling to achieve this). Unfortunately some cells are blank and therefor return 00/Jan/1900. My current iferror will not remove this 00/Jan/1900 - any ideas?

Formula:

=IFERROR(INDEX($B$1:$B$20000,SMALL(IF(ISNUMBER(SEARCH($F$1,$A$1:$A$20000)),MATCH(ROW($A$1:$A$20000),ROW($A$1:$A$20000))),ROW(A1))), "")

If the cell I'm pulling from still has another error I'd still like it to show blank.

Conditional formatting won't work as I've other formulas dependant on returning the cell blank rather than 00/Jan/1900

As always thanks

Stuart
 

Excel Facts

What is the shortcut key for Format Selection?
Ctrl+1 (the number one) will open the Format dialog for whatever is selected.
Try this (Note: untested):

=IFERROR(1/(1/INDEX($B$1:$B$20000,SMALL(IF(ISNUMBER(SEARCH($F$1,$A$1:$A$20000)),MATCH(ROW($A$1:$A$20000),ROW($A$1:$A$20000))),ROW(A1)))),"")
 
Upvote 0
Conditional formatting won't work as I've other formulas dependant on returning the cell blank rather than 00/Jan/1900
What do these other formulas look like?
I am thinking it may be easier to use Conditional Formatting to hide the zero, and then just update the other formulas to deal with the zeroes.
 
Upvote 0
If you click the link you'll see the image.

Basically I'm returning matching values into cells like F3-F8 *matching F1 to range A2-A8, when it finds a match it returns the start date.

Example in F3 I have:
=IFERROR(INDEX($B$1:$B$19998,SMALL(IF(ISNUMBER(SEARCH($F$1,$A$1:$A$19998)),MATCH(ROW($A$1:$A$19998),ROW($A$1:$A$19998))),ROW($A1))), "")

Terrible formula?

When it finds a blank I don't want it return 00/Jan/1900 like it is in F5
 
Upvote 0
Can you just hide 0's using conditional formating? i.e. not make the text blank or something similar.
 
Upvote 0
If you click the link you'll see the image.
Unfortunately, no I won't. My work's security processes block those sites.
 
Upvote 0
Can you just hide 0's using conditional formating? i.e. not make the text blank or something similar.
That is what we were asking, but in your original post you said:
Conditional formatting won't work as I've other formulas dependant on returning the cell blank rather than 00/Jan/1900
which is why I asked:
What do these other formulas look like?
I am thinking it may be easier to use Conditional Formatting to hide the zero, and then just update the other formulas to deal with the zeroes.
 
Upvote 0
Ah,

I'm sorry. I've no idea why my excel add in isn't working to make this easier.

Basically the formula dependant on good dates (not 00/Jan/1900) is:
=IFERROR(SMALL(F3:F8,1),"")

You probably know but that formula grabs the smallest date from the range.
 
Upvote 0

Forum statistics

Threads
1,213,536
Messages
6,114,202
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