Forum Discussion
Solved
Returning adjacent columns with multiple checks in excel
I have 2 columns of data, all numbers. Let's call them "A" and "B" and also a target number I will manually input that will be a single cell, that is my target for "B", let's call this target cell "C1...
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.
I am using office365.
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.
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.