CODEWITHSUNDEEP
excelconditional-statementsmin
user36036
user36036

Reputation: 137

Minimum column value if two other columns match

I have an excel file with data A, B & C.

I want to find the min of C but only if corresponding A=B.

How can I perform this operation?

enter image description here

Upvotes: 0

Views: 91

Answers (1)

user4039065
user4039065

Reputation:

You can accomplish this with an array formula¹. 

=min(if(A2:A34=B2:B34, C2:C34))

Array formulas should never be full column references, If the columns of numbers grows and shrinks occasionally, apply the following to dynamically adjust the number of cells referenced.

=min(if(A2:index(A:A, match(1e99, C:C))=B2:index(B:B, match(1e99, C:C)), C2:index(C:C, match(1e99, C:C))))

¹ Array formulas need to be finalized with Ctrl+Shift+Enter↵. If entered correctly, Excel with wrap the formula in braces (e.g. { and }). You do not type the braces in yourself. Once entered into the first cell correctly, they can be filled or copied down or right just like any other formula. Try and reduce your full-column references to ranges more closely representing the extents of your actual data. Array formulas chew up calculation cycles logarithmically so it is good practise to narrow the referenced ranges to a minimum. See Guidelines and examples of array formulas for more information.

Upvotes: 2

Related Questions