Forum Discussion
Returning adjacent columns with multiple checks in excel
Hi Michael,
On which version of Excel you are? For 2016 and Office365 subscribers such functions as MINIFS are available, for #1 that could be
=MINIFS(A:A,B:B,">="&C1)
For other versions the formula could be like
=MIN(IF($B:$B>=$C$1,A:A))
(array one, Ctrl+Shift+Enter),
or regular (not array)
=AGGREGATE(15,6,($A1:$A19)/($B1:$B19>=$C$1),1)
The rest is similar, depends on your version.
Hi Michael,
On which version of Excel you are? For 2016 and Office365 subscribers such functions as MINIFS are available, for #1 that could be
=MINIFS(A:A,B:B,">="&C1)
For other versions the formula could be like
=MIN(IF($B:$B>=$C$1,A:A))
(array one, Ctrl+Shift+Enter),
or regular (not array)
=AGGREGATE(15,6,($A1:$A19)/($B1:$B19>=$C$1),1)
The rest is similar, depends on your version.
Thank you very much, the MINIF and MAXIF will perfectly handle 1 and 3, now I just need to figure out how to have excel tell me the value of A for which B is a maximum, even though they are not adjacent.
As for adjacent columns - you may use above formulas with any ranges of the same size, e.g. find in A1:A100 the number which will be on the same position here as position of the max number in Z101:Z200.
Michael, that's practically the same:
=MAXIFS(A:A,C:C,MAX(C:C)) {=INDEX(A:A,MATCH(1,--(C:C=MAX(C:C)),0))} =AGGREGATE(15,6,($A:$A)/($C:$C=MAX($C:$C)),1)(second formula is array one).
Sample: