Look Up Conditional Formatted Cell and Return Value of Cell and Cell Next to it

[Lisichka]

Hello! I've tried many different methods for what I am trying to do and none seem to work quite right... Please see below for what I am attempting.

I have a table with two columns- the first column has words and the second column has numbers. In the second column, I have conditional formatting to highlight the three largest numbers.

Let's say the table is in A2:B10 on Sheet 1

In a new table, I want to only show the 3 cells that were highlighted as well as the words they correspond with in the first column. I was able to do this using the below equation:

To get the number for the first largest: =LARGE('Sheet 1'!$B$2:$B$10,1)
For the 2nd largest number: =LARGE('Sheet 1'!$B$2:$B$10,2)

To get the words for the first largest: =INDEX('Sheet 1'!$A$2:$B$10,MATCH(LARGE('Sheet 1'!$B$2:$B$10,1),'Sheet 1'!$B$21:$B$10,0),1)

For the 2nd largest words: =INDEX('Sheet 1'!$A$2:$B$10,MATCH(LARGE('Sheet 1'!$B$2:$B$10,2),'Sheet 1'!$B$21:$B$10,0),1)

The problem I ran into was when I had two numbers that equaled each other. The largest number in the data was '10' and the words next to it said 'a' while the second largest amount was also '10' with the words next to it 'b'. Since the data was the same amount, whenever I tried to pull back the words for the second largest number, I kept getting 'a' for both the largest and second largest.

Is there any way to pull back the words next to a specific cell? Maybe if I do it based off of conditional formatting rather than cell value? But I don't know how to to use an equation to look for conditional formatting... please help!

 

[anglais428]

Assume this data is in range A1:B7

[TABLE="class: grid, width: 500, align: center"]
<tbody>[TR]
[TD]Word[/TD]
[TD]Number[/TD]
[/TR]
[TR]
[TD]I[/TD]
[TD]10[/TD]
[/TR]
[TR]
[TD]Am[/TD]
[TD]20[/TD]
[/TR]
[TR]
[TD]Was[/TD]
[TD]30[/TD]
[/TR]
[TR]
[TD]It[/TD]
[TD]40[/TD]
[/TR]
[TR]
[TD]You[/TD]
[TD]60[/TD]
[/TR]
[TR]
[TD]Time[/TD]
[TD]60[/TD]
[/TR]
</tbody>[/TABLE]
Then in range A12:B15 (to get the top three):

[TABLE="class: outer_border, width: 500, align: center"]
<tbody>[TR]
[TD]Word[/TD]
[TD]Number[/TD]
[/TR]
[TR]
[TD]Time[/TD]
[TD]60[/TD]
[/TR]
[TR]
[TD]You[/TD]
[TD]60[/TD]
[/TR]
[TR]
[TD]It[/TD]
[TD]40[/TD]
[/TR]
</tbody>[/TABLE]


The formula in the second table for the numbers is =LARGE($B$2:$B$7,ROWS($B$13:B13)) then autofill down for the other two cells
The formula in the second table for the word (Time) is =INDEX($A$1:$A$7,SMALL(IF(B13=$B$1:$B$7,ROW($B$1:$B$7)),COUNTIF($B$1:$B$7,B13)-COUNTIF($B$12:B12,B13)))
Confirm this with Control, Shift Enter (not just Enter by itself). Then autofill for the other two cells.

Adjusting the numbers in the first table will adjust the numbers and words in your second table.

Adjust the above to suit your data ranges

 

[cooper645]

I have come up with the following for you.

I set up sample data in B2:B10 and then used;

E2: =LARGE($B$2:$B$10,1)
E3: =LARGE($B$2:$B$10,2)
E4: =LARGE($B$2:$B$10,3)

then in;
D2: =OFFSET($A$2:$A$10,SMALL(IF($B$2:$B$10=$E2,ROW($B$2:$B$10),""),COUNTIF($E$2:E2,E2))-2,0) Use Control+Shift+Enter when entering this as it is an array formula
I then simply dragged it down across D3 & D4.

If you wish for an exact formula you dont have to amend, you will need to supply Sheet names and where the data set is, along with the sheet name and cells you are using for the top 3 lookup.

HTH,

Kindest Regards,

Coops