Find first value that meets some criteria

Is there a worksheet function that will return the index (or offset) of the first value in an array (row or column) that meets some criteria?

For example, suppose I have the following values in C5:Q5:

C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q
1	1	1	1	2	1	1	3	1	1	1	1	1	1	2

I would like a formula that would give me the index (offset) of the first cell that contains a value other than "1" (<>1). In this case, that would be "5". The first value that is not a "1" is in G5, which is the 5th cell from C5.

The Match function almost works, but it requires the values to be in order (ascending or descending).

PS: I actually want it for a column of data, but a row was more compact to display.

Originally Posted by Obsessed

It only needs to be in order if you allow approximate matches. Exact matches can be in any order.

Not sure what you mean by "approximate". The problem as stated was a "not equal" match.

Originally Posted by MarvinP

Hi Cynthia,

Will you accept a helper column in your answer? See the attached that has a helper column.

Thanks for that example. It works, if I adjust to get the offset.

If you can't use a helper then you are going to need to learn Array Formulas.

I can, but I'd prefer a cleaner, more elegant solution. The Match function is so close. It has an "equal" option, a "less than or equal" option, and a "greater than or equal" option. Why didn't the geniuses at M$FT include a "not equal" option?

OK, I've been meaning to learn array formulas. Care to get me started for this problem?

Originally Posted by Tony Valko

Try this array formula:

=MATCH(TRUE,C5:Q5<>1,0)

That works perfectly. I'll go figure out how.

Thanks so much.

Originally Posted by Tony Valko

You're welcome. Thanks for the feedback!

If your question has been solved please mark the thread as being solved.

In the menu bar above the very first post select Thread Tools, then select Mark this thread as solved.

Tony,

I marked the thread solved, but then I decided to take a crack at understanding array formulas.

I marked it unsolved again and attached a little tutorial I wrote about this solution. If you have time, please check it out and let me know if there are any errors or misstatements.

Thanks, C

Originally Posted by Tony Valko

Sorry, I have download size limits so I'm not able to download your file.

Huh? It's only 329 KB!

Here's the Word file. It's only 51 KB.