Excel formula to selectively search a column based on another column?

Question

How would I create this formula to be applied to Column C (returning a boolean)?

If B2 contains "apple", see if the number in A2 exists in any other A cells whose row doesn't contain "apple" in the B column. (Assuming other rows also have "apple" in the B column and may or may not have a duplicate number in the A column).

Here is some example data and the desired result in Column C:

Answer 1

Use IF and COUNTIFS:

=IF(B2="apple",COUNTIFS(A:A,A2,B:B,"<>"&B2)>0,"")

First we check if B2 is "apple"

Second we check if any that are apple then we see if there are any that match A2 that do not contain "apple" in column B

Answer 2

Quick answer:

You can do this using array formulas. In your cell C2, write the following:

=IF(B2="apple",SUM(ISNUMBER(A:A)*(A:A=A2)*(B:B<>B2))>0,"")

then use Ctrl+Shift+Enter to register it as an array formula. Copy it to the rest of the column afterward.

Explanation:

The IF function is the ordinary sort: if in the B column we see “apple”, we do stuff, otherwise just write the empty string. The interesting part is the second argument — that which gives us the result in the “apple” case.

Now first consider what Excel would do with a simpler formula: SUM(A:A=A2). Inside the SUM, Excel sees an equality of the form “range = value”. If both sides were simple values, that would evaluate to a boolean, but here because you used Ctrl+Shift+Enter to turn on array formulas, Excel applies the operation to each element on the left side individually, and stores them in memory in a temporary array, which SUM happily accepts. The result is thus the number of cells in column A that are equal to A2...

Well, almost. This would be the case if SUM treated TRUE values as 1 and FALSE values as 0. That not being the case, we would need to convert the booleans into numbers, one way being a double negation like so: SUM(--(A:A=A2)). The * operator in the actual formula will take care of that too though.

Back to the actual sum, we have the additional term ISNUMBER(A:A). The principle is similar: this will again operate elementwise on column A as it doesn't ordinarily know what to do with a range. Then the * operator “multiplies” the two temporary ranges containing booleans elementwise — basically applying the AND operation to them — and giving us a new range of booleans. (This is here just to ensure that blank cells are not counted as equal to 0, and NOT(ISBLANK(A:A)) would likewise work.)

Finally we do the same with the additional range of booleans that describe whether each individual value in column B is not equal to B2. In the end, SUM then acts on the resulting range.

In conclusion, we counted rows with numbers in the first column, where the current row is match in column A, but not in column B. Your condition just says that the number of these is positive.

Remark 1:
We use the * operator instead of the AND function because the latter happens to accept ranges as argument by default, so it would just eat up the temporary ranges instead of activating the “array mode” and performing an elementwise operation.

Remark 2:
You can freely make the columns absolute for copying the formula to other columns, add (absolute) row numbers to the ranges A:A and B:B to ignore other stuff potentially located in the same column (as well as performance) — or even make named ranges of the two columns like IDs and Fruits, then put these names in the formula instead of the ranges.