I have a Google Sheets file with the following data:

I would like to find all names that have Apple but not Orange, so in this case, John would not be flagged, but Mary would.

The flagging can be by way of highlighting every row that includes Mary via conditional formatting, or by only showing Mary in a pivot table report (as a single entry, or multiple entries equal to the number of lines).

I tried using pivot table and/or VLOOKUP but haven't figured out how to compare values from different rows.

CodePudding user response：

To get a list of names that match your criteria, use filter() and match(), like this:

=filter( 
  A2:A, 
  B2:B = "Apple", 
  isna( 
    match( 
      A2:A, 
      iferror(filter(A2:A, B2:B = "Orange")), 
      0 
    ) 
  ) 
)