How to highlight multiple rows with multiple number matching?

[Rnkhch]

Hello,

I have a table where column A shows sample numbers, column B shows sample names, and columns C to G show other properties of each sample.

In column J, I have a formula that spills down several sample numbers depending on various conditions.

I want all the A:G rows with sample numbers that match the numbers in the spill to be highlighted. I'm having a hard time to come up with the formula. I could easily write one for a single sample number (J1) like this:

=IF(ISNUMBER($J$1),$A1=$J$1) applied to =$A$1:$G$100

But how can I expand this to take into account the other spilled numbers in J?

Thanks for any input!

 

[Peter_SSs]

It really would help in future if you could give us some small sample data and the formula in column J that you refer to. However, see if this dummy sample does what you want.

22 07 09.xlsm
	A	B	C	D	E	F	G	H	I	J
1	No	Name	Other 1	Other 2	Other 3	Other 4	Other 5
2	1	Name 1	93	24	69	59	31			2
3	2	Name 2	59	97	33	67	81			5
4	3	Name 3	67	49	25	94	99			7
5	4	Name 4	26	33	53	32	66			8
6	5	Name 5	2	57	86	24	34
7	6	Name 6	65	33	79	72	73
8	7	Name 7	66	93	73	71	82
9	8	Name 8	2	78	34	44	71
10	9	Name 9	77	39	72	69	36
Highlight

Cell Formulas
Range	Formula
J2:J5	J2	=FILTER(A2:A10,D2:D10>50)
Dynamic array formulas.

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
A2:A10	Expression	=MATCH(A2,$J$2#,0)	text	NO

 

[Rnkhch]

Sure, I will. Thanks, that's exactly what I needed. (I just applied your rule to entire rows A:G, so now whole rows are highlighted)

 

[Peter_SSs]

Glad it helped. Thanks for the follow-up.

BTW, unless you have other data further down column J, this would do just as well as your CF formula.

=MATCH(A2,J:J,0)