Forum Discussion

michael guffey's avatar
michael guffey
Copper Contributor
Mar 24, 2018

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...
Formulas & Functions
Need Help
  • SergeiBaklan's avatar
    SergeiBaklan
    Mar 24, 2018

    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.

Ryan Champagne's avatar
Ryan Champagne
Copper Contributor

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

SergeiBaklan's avatar
SergeiBaklan
MVP
Mar 24, 2018

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

Resources