00/Jan/1900

[stoothom]

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

 

[63falcondude]

Try

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

Changes marked in red.

 

[Joe4]

Just wondering why you are using SMALL instead of MIN, if you are looking for the smallest value. SMALL is good if you do not want the smallest value, but something like the second, third, etc smallest value.

You can use the MIN function and have it ignore zeroes.
Various methods are shown here: Excel Ignore cell with 0 in Min formula

 

[Marcelo Branco]

See if this does what you need

Array formula in F3
=IFERROR(INDEX($B:$B,SMALL(IF(ISNUMBER(SEARCH($F$1,$A$2:$A$20000)),IF(ISNUMBER(B$2:B$20000),ROW(B$2:B$20000))),ROWS(F$3:F3))),"")
Ctrl+Shift+Enter

M.

 

[stoothom]

this was a test to show my problem via excel - didn't work so I've revised the post.

 

[63falcondude]

The formula in post #11 worked when I tested it in cell F3. Here is another one that works and allows you to drag it over to G3 (and down as far as needed).

Excel 2010
	A	B	C	D	E	F	G
1		Start	Finish		Search:	EUS-01-0008
2	EUS-01-0008 ABC	12/8/2015	4/12/2018			start	finish
3	EUS-01-0009 DEF	12/8/2015	4/12/2018		Results:	12/8/2015	4/12/2018
4	EUS-01-0008 GHI	12/8/2015	4/9/2018			12/8/2015	4/9/2018
5	EUS-01-0010 JKL	12/8/2015	4/9/2018			5/19/2017	4/9/2018
6	EUS-01-0008 MNO		4/9/2018
7	EUS-01-0009 PQR		4/9/2018
8	EUS-01-0008 STU	5/19/2017	4/9/2018
Sheet1

Cell Formulas
Range	Formula
F3	{=IFERROR(INDEX(B$2:B$20000,SMALL(IF(($B$2:$B$20000<>"")*ISNUMBER(SEARCH($F$1,$A$2:$A$20000)),ROW($A$2:$A$20000)-1),ROWS($A$1:$A1))),"")}
Press CTRL+SHIFT+ENTER to enter array formulas.

 

[stoothom]

Hi,

I think it's my starting formula might have an issue too.

Entered your change, it seemed to work. I copied it down and it didn't find anything other than the first which after i check isn't the first either.

Confused is an understatement.

 

[stoothom]

THANK YOU.

I'll check it falcon dude - it's likely me, i'm not an expert like all of you, far from it judging by this.

 

[63falcondude]

THANK YOU.

I'll check it falcon dude - it's likely me, i'm not an expert like all of you, far from it judging by this.

You're welcome. Pulling multiple match results can look quite menacing until you get used to it.

 

[stoothom]

Hi,

I tested the formula.

All seemed ok COLUMN F.

I copied the formula down COLUMN G and it seemed to find everything except line 6 and add the 4/9/18 date on it's own.

I've also noticed that if there is a date in column B but not in C then it will automatically still add 0/01/1900 in as a replacement.

In the test you did might you copy your formula further and see what I mean?

This has been testing me literally all day!

 

[63falcondude]

Hi,

I tested the formula.

All seemed ok COLUMN F.

I copied the formula down COLUMN G and it seemed to find everything except line 6 and add the 4/9/18 date on it's own.

I've also noticed that if there is a date in column B but not in C then it will automatically still add 0/01/1900 in as a replacement.

In the test you did might you copy your formula further and see what I mean?

This has been testing me literally all day!

I am happy to help but please do not PM me every time you make a new post.

The formula from post #15 is meant to be placed in cell F3 (with Ctrl + Shift + Enter) and then dragged over to G3 then with F3 and G3 highlighted, dragged down until you get blank cells. This will populate F3:G5 in the sample above (post #15).

Now if there can be blanks in column C as well, then you will have to account for that in the formula. You can include *($C$2:$C$20000<>"") to make sure blank cells in column C are skipped.

Formula would then become

=IFERROR(INDEX(B$2:B$20000,SMALL(IF(($B$2:$B$20000<>"")*($C$2:$C$20000<>"")*ISNUMBER(SEARCH($F$1,$A$2:$A$20000)),ROW($A$2:$A$20000)-1),ROWS($A$1:$A1))),"") Ctrl Shift Enter