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)
#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.
精彩评论