Comparing row values with table array

Question

I have numbers lets say (in one row or in one column)- 1011,3021,5683,9274,1002

Now I have array named "myArray" like below:

1011    68542   136814
3021    112054  79216
5683    136814  1002
68542   79216   9274
144797  1002    1011
98150   9274    98150
112054  112054

Now I want to highlight those numbers from "myArray" which are not listed in 1011,3021,5683,9274,1002 (row or column value)

Can anyone give me the formula or macro?

I tried with conditional formatting, index match, Hlookup, Vlookup....

Answer 1

You can use conditional formatting

1. Create a list A1:A4 -> example 1, 2, 3, 4
2. Create a list B1:B2 -> example 2, 4
3. Create a conditional formatting on the fist list (A1:A4)
4. Select at Format rules Custom formula is
5. Put in Value or formula box =MATCH(A1:A4,B$1:B$2,0)

Now the A2 and A4 (value 2 and 4) should be highlight

---

Return value

A number representing a position in lookup_array.

Syntax

=MATCH (lookup_value, lookup_array, [match_type])

Arguments

• lookup_value - The value to match in lookup_array
• lookup_array - A range of cells or an array reference
• match_type - [optional] How to match, specified as -1, 0, or 1. Default is 1