Returning adjacent columns with multiple checks in excel

[michael guffey]

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"

I drew an example of a graph pointing to the data points I need.

I need to know 3 things from this data:

1. What is the minimum value of "A" which has an adjacent cell in "B" for which "B">="C1" is true.

As a statement, what is the smallest value in A that corresponds with B meeting or exceeded it's target C1.

2. What is the value of "A" that is adjacent to the maximum value of "B"

As a statement, what is the value of A for which B is at it's maximum.

3. What is the maximum value of "A" which has an adjacent cell in "B" for which "B">="C1" is true.

As a statement, what is the greatest value of A that corresponds with B meeting or exceeding it's target C1.


BONUS QUESTION: is it mandatory that "A" and "B" are adjacent to one another? That would save me from having to make a bunch of extra tables.

Thank you all very much, I appreciate any feedback.

[#2 · Accepted solution]

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.

[#3]

I think i understand the problem, but im not sure. 

So you have column A, which would be different manually input things. Then you have B, and you want that to be a generated number so that A + B = C? 

For example: c = 30

Manually input 10 in A, Column B creates 20

Manually input 15 in A, Column B creates 15

Manually input 20 in A, Column B creates 10

[#4]

Ryan,

IMHO not. If do for #1 manually

- you have some numbers in A and B plus C1=30;

- filter B on numbers >=30;

- sort A (without expanding) from A to Z

First number in A will be an answer

[#5]

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.

[#6]

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:

[#7]

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.