Help - MATCH not equal to

[Dannyh1]

Hi,

I have a column of cells all containing the word OUT except for 1 cell.

What is the MATCH syntax to find the cell without this word?

Many Thanks,

 

[CWatts]

Here's a UDF solution:

Worksheet syntax: =DiffWord(A1:A21,"Out")

It's set up so you can customize the word you want to use as the "right" word and will find the first deviation in the range of that word.

Function DiffWord(rng As Range, word As String) As Integer
DiffWord = 0
For Each c In rng
    DiffWord = DiffWord + 1
    If c.Value <> word Then Exit For
Next
End Function

 

[Aladin Akyurek]

Hi,

I have a column of cells all containing the word OUT except for 1 cell.

What is the MATCH syntax to find the cell without this word?

Many Thanks,

Control+shift+enter, not just enter:

=MATCH(1,IF(A2:A100<>"OUT",IF(A2:A100<>"",1)),0)

yields the position of the first cell which does not house an isntance of OUT.

If you want to fetch the item of interest...

=LOOKUP(9.99999999999999E+307,IF(A2:A100="OUT","#",IF(A2:A100="","#",1)),A2:A100)

 

[T. Valko]

Hi,

I have a column of cells all containing the word OUT except for 1 cell.

What is the MATCH syntax to find the cell without this word?

Many Thanks,

Is this what you had in mind...

Book1
	A
2	out
3	out
4	out
5	out
6	out
7	out
8	_
9	out
10	out
Sheet1

This array formula**:

=MATCH(TRUE,A2:A10<>"out",0)

** array formulas need to be entered using the key
combination of CTRL,SHIFT,ENTER (not just ENTER).
Hold down both the CTRL key and the SHIFT key
then hit ENTER.

Result = 7

The cell that does not contain "out" is the 7th cell in the range.