Super User is a question and answer site for computer enthusiasts and power users. Join them; it only takes a minute:

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top

I have a block of data that represents the steps in a process and the possible errors:

ProcessStep   Status
FeesPaid      OK
FormRecvd     OK
RoleAssigned  OK
CheckedIn     Not Checked In.
ReadyToStart  Not Ready for Start

I want to find the first Status that is not "OK".

I have attempted this:

=Match("<>""OK""", StatusRange, 0)
which is supposed to return the index of the first element in the range that is NOT-EQUAL (<>) to "OK"
But this doesn't work, instead returning #N/A.

I expect it to return 4 (index #4, in a 1-based index, representing that CheckedIn is the first non-OK element)

Any ideas how to do this?

share|improve this question

Suppose cells to check are in A1:A10. Then it suffices to use the following formula:

=MATCH(0,IF(A1:A10="OK",1,0),0)

to be entered by Ctrl+Shift+Enter

share|improve this answer

There's at least one easy way to do this, provided you don't mind setting up another block of (possibly hidden) cells somewhere. For example, you could add a formula column as follows, which tests whether the cell to its left is equal to "OK":

|   | A              | B        | C        |
|---+----------------+----------+----------|
| 1 | "ProcessStep"  | "Status" |          |
| 2 | "FeesPaid"     | "OK"     | =B2="OK" |
| 3 | "FormRecvd"    | "OK"     | =B3="OK" |
| 4 | "RoleAssigned" | "OK"     | =B4="OK" |
| 5 | "CheckedIn"    | "OK"     | =B5="OK" |
| 6 | "ReadyToStart" | "OK"     | =B6="OK" |

Now you can use =MATCH(FALSE,C2:C6,0) to find the row containing the first non-OK status.

EDIT: Toc has a better solution.

share|improve this answer

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .