开发者

Excel Matching problem with logic expression

(I understand Excel is only borderline programming)

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 r开发者_开发知识库eturn 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?


I think this and other similar questions are completely legitimate programming questions (EDIT: see here: https://meta.stackexchange.com/questions/22922/which-site-do-excel-or-other-spreadsheet-formulas-belong-on/76767#76767). (It's probably a duplicate of some other StackOverflow question, though.)

You want to use an array formula:

=MATCH(TRUE,(StatusRange<>"OK"),0)

You need to enter this as an array formula, with Ctrl-Shift-Enter.

'MATCH' finds a value in a range or an array. Comparing a range to a scalar, as in '(StatusRange<>"OK")', returns an array of boolean values, so you're looking to match a value of 'TRUE'.

(The formula you posted was looking for a string literal with the value '<>"OK"'...)

If you ultimately want the value in the ProcessStep column, look at the help for the 'INDEX' or 'VLOOKUP' functions.

0

上一篇:

下一篇:

精彩评论

暂无评论...
验证码 换一张
取 消

最新问答

问答排行榜