CODEWITHSUNDEEP
excelexcel-formulaarray-formulas
c.dej
c.dej

Reputation: 3

Index/match if greater than

Hoping someone can help me with my trouble with an index/match formula.

Column B has a list of names, column C has the frequency of an action taken (sales made) whilst column D has the average sales value.

I've created in column G a track of the 6 highest sales which has worked great:

=LARGE($D$8:$D$13, 2)

Then I've used column F to determine the name that matches each sales average:

=INDEX($B$8:$B$13, MATCH(G4, $D$8:$D$13, 0))

So far so good! However, I'd like to only include the sales average if that individual has had more than 3 sales. IE; the value in column C is >3.

Can any one provide help or suggestions please?

Upvotes: 0

Views: 2231

Answers (2)

Ulli Schmid
Ulli Schmid

Reputation: 1167

You could use an array formula:

=LARGE(IF(range=criteria,values),n)

so for your case:

=LARGE(IF($C$8:$C$13>3,$D$8:$D$13), 2)

press CTRL+SHIFT+ENTER to enter an array formula.

Upvotes: 0

ttaaoossuu
ttaaoossuu

Reputation: 7884

I think you could use a helper column to filter out the values you don't need before applying =LARGE() in the very beginning, like this:

=IF($C8>3, $D8, "")

Then do =LARGE() to this column instead:

=LARGE($X$8:$X$13, 2)

Upvotes: 1

Related Questions