array based on max and less than?

Question

I'm not sure if I'm over complicating this... basically I'd like to have a formula which is if the c column is less than 6, then look up the max value in B but display the value of C

so far I have this but I'd like it to show 2, not 437

{=MAX(IF(C2:C12<6,B2:B12, 0))}

any advice is appreciated. i'm shy, be nice..thanks

A       B       C 
cat     110     3
dog     148     4
rooster  36     7
duck     32     8
pig     437     2
horse    44     6
eagle   215     5
dolphin  21     1
panda     2     9
iguana  257    10
fish    199    11

edit:

maybe something like {=INDEX(C2:C12,MATCH(MAX(IF(C2:C12<6,C2:C12)),C2:C12,0))}

but I don't see where to put b2:b12

barry houdini · Accepted Answer · 2018-01-18 00:25:00Z

0

You really need two conditions

1) Column B is equal to =MAX(IF(C2:C12<6,B2:B12))

2) Column C is <6

so you can INDEX column C when those two are met, i.e.

=INDEX(C2:C12,MATCH(1,(B2:B12=MAX(IF(C2:C12<6,B2:B12)))*(C2:C12<6),0))

confirmed with CTRL+SHIFT+ENTER

answered Jan 18, 2018 at 0:25

barry houdini

46.9k8 gold badges67 silver badges84 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

holly olly Over a year ago

WORKS omg thank you, this was a lot more complicated than i thought!!!!

holly olly Over a year ago

i tried to give you a point too but i don't have enough posts, can't thank you enough

AmBo · Accepted Answer · 2018-01-17 23:40:11Z

0

{=IF(C2<6,INDEX($C$2:$C$12,MATCH(MAX($B$2:$B$12),$B$2:$B$12,0)),0)}

answered Jan 17, 2018 at 23:40

AmBo

1213 bronze badges

3 Comments

holly olly Over a year ago

this is close but it gives me 10 instead of 2? i think it's looking at the entire b column

AmBo Over a year ago

I think it depends on how you put your formula, should work fine if you put it in D2 then copy paste down, if you want to validate D2:D12 at once use {=IF(C2:C12<6,INDEX($C$2:$C$12,MATCH(MAX($B$2:$B$12),$B$2:$B$12,0)),0)}

Blackwood Over a year ago

This would be a better answer if you explain how it works.

K.Dᴀᴠɪs · Accepted Answer · 2018-01-17 23:48:26Z

0

You were almost there.. Basically if C<6 , find max of B , lookup it in B:C and display corresponding C

{=IFERROR(VLOOKUP(MAX(IF(C2:C12<6,B2:B12, 0)),B2:C12,2,FALSE),0)}

Since your question doesn't clarify what if c>=6 I assume you don't want value. Can answer more precisely if you clarify.

Mark this as answer if that's correct. Hope this helps!

edited Jan 17, 2018 at 23:48

K.Dᴀᴠɪs

10.1k13 gold badges36 silver badges46 bronze badges

answered Jan 17, 2018 at 23:21

Amit Chauhan

12 bronze badges

3 Comments

Amit Chauhan Over a year ago

make sure column b dont have 0 value.. as if clause returns 0 and vlookup will still bring that value. In that case replace 0 with some other value in this formula.e.g. {=IFERROR(VLOOKUP(MAX(IF(C2:C12<6,B2:B12, -1)),B2:C12,2,FALSE),-1)}

holly olly Over a year ago

is it possible to use index instead of vlookup...like =INDEX(C2:C12,MATCH(MAX(IF(C2:C12<6,C2:C12)),C2:C12,0)) is close but i don't see where I put b2:b12? Thank you!

Glitch_Doctor Over a year ago

@hollyolly Please see my answer if you would like a single INDEX() solution

Glitch_Doctor · Accepted Answer · 2018-01-18 10:05:10Z

0

As I could see you requested a single INDEX formula:

{=INDEX($C$2:$C$12,MATCH(MAX(IF($C$2:$C$12<6,$B$2:$B$12,0),0),IF($C$2:$C$12<6,$B$2:$B$12,0),0))}

This is an array formula, hit Ctrl+Shift+Enter while still in the formula bar.

Lets break this down.

=INDEX(C:C, - Index column C as these are the values you want returned

MATCH(IF(C:C<6,B:B,0), - Find the largest value from the following array in the array and return it's relative position for INDEX()

IF(C:C<6,B:B,0),0)) - If the value in column c is less than 6 then add the column B value to the array, otherwise add 0

edited Jan 18, 2018 at 10:05

answered Jan 18, 2018 at 9:52

Glitch_Doctor

3,0443 gold badges19 silver badges31 bronze badges

2 Comments

holly olly Over a year ago

Thanks! I missed this

holly olly Over a year ago

Works great. Thanks for the detailed explanation..when I get enough credits I'll give you a point. I'm not sure if I can select 2 answers as correct.

Collectives™ on Stack Overflow

array based on max and less than?

4 Answers 4

2 Comments

3 Comments

3 Comments

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

2 Comments

3 Comments

3 Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related