Formula that do not include 1,2 and 3.

[serge]

Hi, I have this formula that work fine for me ( in Blue table ), but now I need to tweak it for not returning the digits : 1,2,3 .( in green table starting in S6.

See example below. Thank you.

Excel Workbook
	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U	V
5
6		8	7	2	5	8			2	5	7	8					5	7	8
7		1	2	6	7	3			1	2	3	6	7				6	7
8		1	3	0	7	5			0	1	3	5	7				5	7	0
9
Sheet

 

[serge]

Sorry, it didn't display the whole formula.

=IF(ISERROR(FIND(0,$D6&$E6&$F6&$G6&$H6&$I6,1)),IF(ISERROR(FIND(1,$D6&$E6&$F6&$G6&$H6&$I6,1)),IF(ISERROR(FIND(2,$D6&$E6&$F6&$G6&$H6&$I6,1)),IF(ISERROR(FIND(3,$D6&$E6&$F6&$G6&$H6&$I6,1)),IF(ISERROR(FIND(4,$D6&$E6&$F6&$G6&$H6&$I6,1)),IF(ISERROR(FIND(5,$D6&$E6&$F6&$G6&$H6&$I6,1)),"",5),4),3),2),1),0)

 

[markmzz]

Hi Serge!

How are you?

Try this:

In K6:
=IFERROR(SMALL(IF(ISNUMBER(MATCH(COLUMN($D$6:$H$6)-COLUMN($D$6)+1,MATCH($D6:$H6,$D6:$H6,0),0)),$D6:$H6),COLUMNS($K$6:K$6)),"")

In S6:
=IFERROR(SMALL(IF(ISNUMBER(MATCH(COLUMN($D$6:$H$6)-COLUMN($D$6)+1,MATCH($D6:$H6,$D6:$H6,0),0)),IF(($D6:$H6<1)+($D6:$H6>3),$D6:$H6)),COLUMNS($S$6:S$6)),"")

Markmzz

 

[markmzz]

Serge,

I forgot: Array Formula - use Ctrl+Shift+Enter to enter the formula.

Markmzz

 

[serge]

Hi Mark,

I'm fine thank you, hope you too,

Your formulas work great thank you again, like always very satisfied.
Serge.

 

[markmzz]

Hi Mark,

I'm fine thank you, hope you too,

Your formulas work great thank you again, like always very satisfied.
Serge.

Serge,

I'm fine too, thank you.

Thank's too for the feedback and your nice words.

Markmzz