How do I see what's missing in a list?

[aristotle2600]

Let’s say I have a list of text items in A1:A5. Then in B1:B5, I have another list, of items from the first list. I would like to scan through the B list, and see what if any items are missing. For example, let’s say that my A column has

Apple
Banana
Cherry
Date
Elderberry

Then in the B column I might have

Banana
Elderberry
Cherry
Date
Cherry

I already know how to alert on the fact that Cherry appears twice, but I also want a cell to show the missing entry, Apple. How do you do this?

[PYS]

HI

Two techniques are illustrated in the Missing.ods attached.

1. Use e.g. =IF(COUNTIF(B2:B6;A2:A6);"";A2:A6) to show what is missing (matrix formula, so validation with Ctrl+Shift+Enter)
2. Conditional Formatting for A2:A6, formula ISNA(MATCH(A2;$B$2:$B$6;0))

Regards

[aristotle2600]

Thanks; I’ve seen matrix formulas used in solutions from time to time, but I don’t know really what they are. Where might I learn more about them? Just in the help somewhere?

[PYS]

Definition, syntax & examples here