Finding occurance in formula when NOT A1

[surkdidat]

** PLease remove, have just self-solved**
I am using the following formula to extract a list of all values that are currently in cell A1 (This is a dopdown of about 10 different values) - and works fine.

However, is there an easy way to adapt this to do the reverse? (So wil perform the action if it IS NOT the same value as A1?

=INDEX('In Progress'!$A$2:$A$50,SMALL(IF(IFERROR(FIND($A$1,'In Progress'!$L$2:$L$50),0)>0,ROW('In Progress'!$E$2:$E$50)-ROW($A$2)+1),ROW(1:1)))

 

[KRice]

I'm not quite sure that I understand what you want, but have a look at this approach. For a sample worksheet [In Progress] that looks like this:

Book2
	A											L
1
2	2											cat
3	3											dog
4	4											cat
5	5											dog
6	6											cat
7	7											dog
8
9
10
In Progress

You can construct a list of values from column A whose corresponding contents in column L either match (see formula in D1 and down) or do not match (see formula in E1 and down).

Book2
	A	B	C	D	E
1	cat		2	2	3
2			4	4	5
3			6	6	7
4			#NUM!	#NUM!	0
5			#NUM!	#NUM!	0
Sheet2

Cell Formulas
Range	Formula
C1:C5	C1	=INDEX('In Progress'!$A$2:$A$50,SMALL(IF(IFERROR(FIND($A$1,'In Progress'!$L$2:$L$50),0)>0,ROW('In Progress'!$E$2:$E$50)-ROW($A$2)+1),ROW(1:1)))
D1:D5	D1	=INDEX('In Progress'!$A$2:$A$50,AGGREGATE(15,6,(ROW('In Progress'!$E$2:$E$50)-ROW('In Progress'!$E$2)+1)/('In Progress'!$L$2:$L$50=$A$1),ROWS(D$1:D1)))
E1:E5	E1	=INDEX('In Progress'!$A$2:$A$50,AGGREGATE(15,6,(ROW('In Progress'!$E$2:$E$50)-ROW('In Progress'!$E$2)+1)/('In Progress'!$L$2:$L$50<>$A$1),ROWS(E$1:E1)))

How do want to handle blanks? As you'll see, a blank in the A column is reported back in the results as either an error or a 0, so some additional logic would be necessary to handle that condition. And to clarify, do you want the L column values to match the $A$1 value, or are you trying to determine whether a portion of the text strings in the L column matches $A$1. In other words, if $A$1 is "cat" would "bearcat" be considered a match?