1

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.

1 Answer 1

1

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")

example

3
  • 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, 2014 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.) Apr 24, 2014 at 16:30
  • @user2521252 No problem! I'm glad you were able to get it to work.
    – bdr9
    Apr 24, 2014 at 16:42

You must log in to answer this question.

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