I want to write a formula that will do the following:

Search for a POS in column C in rows where ID=ID. If a POS is found, output "yes" in the haspos? column, otherwise output "no".

Here is what the data looks like:

      A        B         C
row ----------------------------- 
1   | ID  |  hasPOS? | Result
2     1       yes      NEG
3     1       yes      NEG
4     1       yes      POS
5     2       yes      NEG
6     2       yes      POS
7     2       yes      BLANK
8     2       yes      BLANK
9     3       no       NEG
10    3       no       NEG

So for example, for ID = 1, the formula would search C2, C3 and C4 for a POS. If found it will output "yes" in B2, B3 and B4. Can this be done without resorting to VBA?

Any insight is appreciated. Thanks.


The following array formula can be entered into column B in order to achieve the effect you want. Because this is an array formula, be sure to select the entire range B1:B9 when you enter it, and then press Ctrl+Shift+Enter once the formula is entered.

=IF(ISERROR(MATCH($A$1:$A$9&"POS", $A$1:$A$9&$C$1:$C$9, 0)), "no", "yes")


  • My original answer involved a helper formula in column D, but this method doesn't require one. If you are not comfortable with array formulas, feel free to view the revision history of this post and use my previous method. – bdr9 Apr 23 '14 at 21:29
  • Thank you for your help. I just tried the formula but it does not work for ID = 3. It still returns "yes" even though this ID only has NEG. I used the exact data set in your screenshot above, and I entered the formula as an array formula. Is it because I'm using excel 2003? EDIT: Nevermind, I got it to work: It's because I wasn't selecting the entire array of B1:B9, sorry! (and thanks again.) – user2521252 Apr 24 '14 at 16:30
  • @user2521252 No problem! I'm glad you were able to get it to work. – bdr9 Apr 24 '14 at 16:42

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.