Find all values greater or equal than a certain value

Question

Let's say I have the following table.

Team Score
AA   81
BB   67
CC   44
DD   1.5
JJ   279
LL   49
TT   201
GG   158
MM   32
HH   89

I want to get all teams that scored more than 80 in another table. I tried the Index + Match function as follows but it only gives me the smallest value greater than 80.

Here is the code:

=INDEX($A$2:$A$11,MATCH(80,$B$2:$B$11,-1))

Although I put the values in the lookup_array argument in descending order, this function only gives me one answer: The smallest value greater than 80.

I am trying to do this without having to sort the array and with a huge database mind you. Thank you.

Solution 1

How about using an AutoFilter? You're then quite flexible in filtering for values in column score.

Solution 2

Enter this formula to D2 and press CTRL+SHIFT+ENTER to make it an array formula, then drag it down.

=INDEX($A$1:$A$11,SMALL(IF($B$2:$B$11>=80,ROW($B$2:$B$11)),ROW(1:1)))

See How to look up a value in a list and return multiple corresponding values for details.

Solution 3

If you don't want to use vba, in a third column you could have

=if(B2 > 80, A2, "")

and copy that formula down. You could even put this column of formulas in another tab and either delete the rows that have nothing in them manually or write code that will do that for you.