Excel: INDEX and MATCH on rows matching criteria

Question

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.

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