Small Lookup with 2 range criteria

[cgsierra]

Hello,
I'm trying to put together a formula that will look at 2 columns and based on criteria for those columns to return the corresponding value of a 3rd column.
IE. I want to retrieve the smallest revenue of only subtotal clients with an Operating Income of 0.
Column A: Client, Column B: Revenue, Column C: Operating Income
A1: XXXX B1: 50 C1: 20
A2: Subtotal B2: 200 C2: 0
A3: YYYY B3: 55 C3: 24
A4: ZZZZ B4: 480 C4: 0
A5: Subtotal B5: 30 C5: 0
A6: Subtotal B6: 95 C6: 40

In this case, the answer would be 30.
I am using the following formula but it does not work well.
={SMALL(IF(AND(A:A="Sub-total",C:C=0), B:B, 9E+99), 1)}
thank you!

 

[Haseeb Avarakkan]

Hello, Try thi Array Formula.

=MIN(IF(A1:A100="Sub-total",IF(C1:C100=0,B1:B100)))

 

[T. Valko]

Hello,
I'm trying to put together a formula that will look at 2 columns and based on criteria for those columns to return the corresponding value of a 3rd column.
IE. I want to retrieve the smallest revenue of only subtotal clients with an Operating Income of 0.
Column A: Client, Column B: Revenue, Column C: Operating Income
A1: XXXX B1: 50 C1: 20
A2: Subtotal B2: 200 C2: 0
A3: YYYY B3: 55 C3: 24
A4: ZZZZ B4: 480 C4: 0
A5: Subtotal B5: 30 C5: 0
A6: Subtotal B6: 95 C6: 40

In this case, the answer would be 30.
I am using the following formula but it does not work well.
={SMALL(IF(AND(A:A="Sub-total",C:C=0), B:B, 9E+99), 1)}
thank you!

Don't use entire columns for this unless you have data in every row!

Array entered**:

=MIN(IF(A1:A6="Sub-total",IF(C1:C6=0,B1:B6)))

** array formulas need to be entered using the key combination of CTRL,SHIFT,ENTER (not just ENTER). Hold down both the CTRL key and the SHIFT key then hit ENTER.

I'm assuming column C will not have any empty cells within the data range.

 

[cgsierra]

Thank you,
I was actually hoping to be able to use the SMALL function as I will also need to retrieve the second smallest, 3rd smallest and so on. Also, I do have spaces in the data ranges.
thanks!

 

[cgsierra]

Thank you,
I was actually hoping to be able to use the SMALL function as I will also need to retrieve the second smallest, 3rd smallest and so on. Also, I do have spaces in the data ranges.
thanks!

By the way, both your formulas worked great, I just tweaked it a little to use the SMALL function! Thanks again!!

 

[T. Valko]

Thank you,
I was actually hoping to be able to use the SMALL function as I will also need to retrieve the second smallest, 3rd smallest and so on. Also, I do have spaces in the data ranges.
thanks!

If column C contains empty cells they will evaluate as being equal to 0.

So, you might have to add an additional test to account for that.

Still array entered**:

=SMALL(IF(A1:A6="Sub-total",IF(C1:C6<>"",IF(C1:C6=0,B1:B6))),n)

Where n = the nth smallest

** array formulas need to be entered using the key combination of CTRL,SHIFT,ENTER (not just ENTER). Hold down both the CTRL key and the SHIFT key then hit ENTER.