Conditional formatting in Excel based on formatted output in other table

Question

Say,I have 2 tables...Tables are related...

 1  3  5  7
 8  8  9 10
11 12 13 14

&

2 3 5 5
1 1 1 1
3 4 4 4

I have highlighted top 5 values in first table using conditional formatting rules.Now I want to highlight cells in the second table based on first table...Assume 11 12 13 14 7 10 are highlighted in first table....I want to highlight cells in second table occupied same position as of in highlighted cells in first table..i.e. 3 4 4 4 5 1 should be highlighted...How can I do that

Answer 1

Use Excel functions RANK and/or COUNTIF to calculate a sequential ranking list column for the first table. The expression is explained here.

For the second table, you can then use conditional formatting based on the computed rank.

Answer 2

Might just be an elaboration of @Axel's answer and I agree the details of the question do not make sense but the position of a selection of top (or bottom) ranked cells in one array (say A1:D3) can be used to determine whether or not to format a cell in the corresponding position of another array (say H5:K7):

Select from H5 to K7, then HOME > Styles - Conditional Formatting, New Rule..., Use a formula to determine which cells to format and Format values where this formula is true::

=RANK(A1,$A$1:$D$3,)<6

Format..., select formatting, OK, OK.

Change the 6 to suit how many cells are to be formatted which for <6 could be more than five if the value ranked fifth is duplicated.