Conditionally format a cell based on another cells conditional format

[sclark27]

See link below with notes. The percentages in column E are conditionally formatted to show the top number of values called for in cell E3. How do I conditionally format the cells in column B that correspond to the numbers in column D that are the highest percentages?

Untitled spreadsheet

Sheet3 Top,3 4 6,0,18.42% 3,1,18.42% 3,2,40.63% 0,3,40.63% 2,4,28.57% 4,5,15.38% 4,6,28.57% 4,7,36.36% 2,8,9.76% 4,9,18.42% 3 2 6 7 3,Column E is conditionally formatted to highlight the top 3 percentages 5 7,How do I conditionally format the numbers in column B that match the assigned numbers ...

docs.google.com

 

[etaf]

just use the same rule in column B
=$E5>=LARGE($E$5:$E$14,$E$3)
to highlight B

or is that NOT what you meant

Untitled spreadsheet.xlsx
	A	B	C	D	E
1
2
3				Top	3
4		4
5		6		0	18.42%
6		3		1	18.42%
7		3		2	40.63%
8		0		3	40.63%
9		2		4	28.57%
10		4		5	15.38%
11		4		6	28.57%
12		4		7	36.36%
13		2		8	9.76%
14		4		9	18.42%
Sheet3

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
B5:B14	Expression	=$E5>=LARGE($E$5:$E$14,$E$3)	text	NO
E5:E14	Expression	=$E5>=LARGE($E$5:$E$14,$E$3)	text	NO

Cells with Data Validation
Cell	Allow	Criteria
E3	List	1,2,3,4

for 2007, 2010 , 2013 , 2016 , 2019 or 365 Subscription excel version
Conditional Formatting

Highlight applicable range >>
B5:B100 - Change, reduce or extend the rows to meet your data range of rows

Home Tab >> Styles >> Conditional Formatting
New Rule >> Use a formula to determine which cells to format
Edit the Rule Description: Format values where this formula is true:
=$E5>=LARGE($E$5:$E$14,$E$3)

Format [Number, Font, Border, Fill]
choose the format you would like to apply when the condition is true
OK >> OK

 

[sclark27]

So the top three percentages are in cells E7, E8, & E12. The corresponding numbers are 2, 3, & 7 respectively from column D. I would like the same corresponding numbers in column B to also highlight. In this case any cell that has the number 2, 3, or 7.

 

[etaf]

what version of excel
this may be a messy answer - BUT needs to have the FILTER() function

AND another member maybe able to see a MUCH simpler solution

=ISNUMBER(MATCH($B4,FILTER($D$5:$D$14,$E$5:$E$14>=LARGE($E$5:$E$14,$E$3)),0))

maybe very slow and may need a helper column

Untitled spreadsheet.xlsx
	A	B	C	D	E	F	G
1
2
3				Top	3
4	FALSE	4	0				FILTER - extract matching number FYI
5	FALSE	6	0	0	18.42%		2
6	TRUE	3	1	1	18.42%		3
7	TRUE	3	1	2	40.63%		7
8	FALSE	0	0	3	40.63%
9	TRUE	2	1	4	28.57%
10	FALSE	4	0	5	15.38%
11	FALSE	4	0	6	28.57%
12	FALSE	4	0	7	36.36%
13	TRUE	2	1	8	9.76%
14	FALSE	4	0	9	18.42%
15	TRUE	3	1
16	TRUE	2	1
17	FALSE	6	0
18	TRUE	7	1
19	TRUE	3	1
20	FALSE	5	0
21	TRUE	7	1
22	FALSE	9	0
23	FALSE	5	0
24	FALSE	8	0
25	TRUE	3	1
26	FALSE	1	0
27	FALSE	1	0
28	TRUE	3	1
29	FALSE	6	0
30	TRUE	3	1
31	FALSE	6	0
32	FALSE	0	0
33	TRUE	2	1
34	FALSE	0	0
35	TRUE	3	1
36	TRUE	2	1
37	TRUE	7	1
38	TRUE	3	1
39	FALSE	6	0
40	FALSE	9	0
41	TRUE	2	1
42	TRUE	2	1
43	FALSE	1	0
44	TRUE	2	1
45	FALSE	8	0
46	FALSE	4	0
47	FALSE	0	0
48	FALSE	8	0
49	FALSE	4	0
50	FALSE	5	0
51	FALSE	1	0
52	TRUE	3	1
53	FALSE	6	0
54	TRUE	2	1
55	TRUE	7	1
56	TRUE	7	1
57	TRUE	7	1
58	TRUE	7	1
59	TRUE	2	1
60	FALSE	9	0
61	TRUE	3	1
62	FALSE	6	0
63	FALSE	9	0
64	FALSE	0	0
65	FALSE	5	0
66	FALSE	9	0
67	FALSE	1	0
68	FALSE	4	0
69	TRUE	7	1
70	FALSE	6	0
71	TRUE	2	1
72	FALSE	0	0
73	FALSE	6	0
Sheet3

Cell Formulas
Range	Formula
G5:G7	G5	=FILTER($D$5:$D$14,$E$5:$E$14>=LARGE($E$5:$E$14,$E$3))
A4:A73	A4	=ISNUMBER(MATCH($B4,FILTER($D$5:$D$14,$E$5:$E$14>=LARGE($E$5:$E$14,$E$3)),0))
C4:C73	C4	=SUM(COUNTIFS(B4,$G$5#))
Dynamic array formulas.

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
B4:B73	Expression	=ISNUMBER(MATCH($B4,FILTER($D$5:$D$14,$E$5:$E$14>=LARGE($E$5:$E$14,$E$3)),0))	text	YES
E5:E14	Expression	=$E5>=LARGE($E$5:$E$14,$E$3)	text	NO

Cells with Data Validation
Cell	Allow	Criteria
E3	List	1,2,3,4

OR you can use a helper column and use the extreacted numbers that match
=SUM(COUNTIFS(B4,$G$5#))

Untitled spreadsheet.xlsx
	A	B	C	D	E	F	G
1
2
3				Top	3
4	0	4	0				Helper column
5	0	6	0	0	18.42%		2
6	1	3	1	1	18.42%		3
7	1	3	1	2	40.63%		7
8	0	0	0	3	40.63%
9	1	2	1	4	28.57%
10	0	4	0	5	15.38%
11	0	4	0	6	28.57%
12	0	4	0	7	36.36%
13	1	2	1	8	9.76%
14	0	4	0	9	18.42%
15	1	3	1
16	1	2	1
17	0	6	0
18	1	7	1
19	1	3	1
20	0	5	0
21	1	7	1
22	0	9	0
23	0	5	0
24	0	8	0
25	1	3	1
26	0	1	0
27	0	1	0
28	1	3	1
29	0	6	0
30	1	3	1
31	0	6	0
32	0	0	0
33	1	2	1
34	0	0	0
35	1	3	1
36	1	2	1
37	1	7	1
38	1	3	1
39	0	6	0
40	0	9	0
41	1	2	1
42	1	2	1
43	0	1	0
44	1	2	1
45	0	8	0
46	0	4	0
47	0	0	0
48	0	8	0
49	0	4	0
50	0	5	0
51	0	1	0
52	1	3	1
53	0	6	0
54	1	2	1
55	1	7	1
56	1	7	1
57	1	7	1
58	1	7	1
59	1	2	1
60	0	9	0
61	1	3	1
62	0	6	0
63	0	9	0
64	0	0	0
65	0	5	0
66	0	9	0
67	0	1	0
68	0	4	0
69	1	7	1
70	0	6	0
71	1	2	1
72	0	0	0
73	0	6	0
Sheet3 (2)

Cell Formulas
Range	Formula
G5:G7	G5	=FILTER($D$5:$D$14,$E$5:$E$14>=LARGE($E$5:$E$14,$E$3))
A4:A73	A4	=SUM(COUNTIFS(B4,$G$5#))
C4:C73	C4	=SUM(COUNTIFS(B4,$G$5#))
Dynamic array formulas.

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
B4:B73	Expression	=SUM(COUNTIFS($B4,$G$5#))	text	YES
E5:E14	Expression	=$E5>=LARGE($E$5:$E$14,$E$3)	text	NO

Cells with Data Validation
Cell	Allow	Criteria
E3	List	1,2,3,4

and if an earlier version - should be able to pull out the list without an FILTER()

 

[etaf]

maybe useful to see a sample spreadsheet
on dropbox for a few days

2 sheets - showing each xl2bb above

condfrmt_Based on large and lookup in a list -ETAF.xlsx

Shared with Dropbox

www.dropbox.com

 

[sclark27]

Good information. Didn't use verbatim but was able to tweak it to work based on what you provided. I did use a helper column.

 

[etaf]

great you are welcome

i would still add the version of excel
What version of excel are you using - would be worth updating your profile to show version as solutions will be dependant on what version you have and therefore what functions available.
Excel Versions are: 2003/2007/2010/2013/2016/2019/2021/2024 (due April24) /365 subscription , also rather than show more than 1 version . if you have them , show which version you will be using the solution for.
An awful lot of new functions have now been added to the newer versions