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.

In all honesty, this is best done with VBA, not a set of formulas. — Kveld Ulf, May 29 '14 at 19:39

score 4 · Answer 1 · answered May 29 '14 at 20:16

4

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)))

enter image description here

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

answered May 29 '14 at 20:16

zx8754

52,746
12
114
209

1

Simplest and gives answer with array formula. +1 – eggy Aug 09 '19 at 14:01

score 2 · Answer 2 · answered May 29 '14 at 19:48

2

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

answered May 29 '14 at 19:48

mthierer

594
4
9

score 1 · Answer 3 · answered May 29 '14 at 19:42

1

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.

answered May 29 '14 at 19:42

Matt Cremeens

4,951
7
38
67

It would be best, but I am not sure this asker is after that solution. If it were me, and I guess you, @Quester, I would do something like this `nextRow = 1 for i = 1 to 10 if cells(i,2) > 80 then cells(nextRow, 3)=cells(i,1) nextRow=nextRow+1 end if next i` – Matt Cremeens May 29 '14 at 19:56

score 0 · Answer 4 · answered May 29 '14 at 19:51

0

Create a PivotTable (Team for ROWS, Score for Sigma VALUES, then filter the Sum of Score column for Number Filters, Greater Than..., and 80 next to Show rows where: is greater than *, OK.

* or perhaps is greater than or equal to.

answered May 29 '14 at 19:51

pnuts

58,317
11
87
139

Find all values greater or equal than a certain value

4 Answers4

Linked