CODEWITHSUNDEEP
arraysexcel
M. Glatki
M. Glatki

Reputation: 787

Finding key for minimum value and conditions in excel

This is my table (copied from the similar question Finding minimum value in index(match) array [EXCEL])

A                           B    C    D
tasmania                    10   3    10
queensland                  22   8    10
new south wales             10   12   12
northern territory           8   4    15
south australia             12   2    8
western australia           32   4    15
tasmania                    72   6    16

I have criteria for B and C, and I want to retrieve the A with the lowest corresponding value D. Values in B, C and D can be duplicates, values in A can not.

Example:

B >= 8
C >= 4

Should result in "queensland" (lowest matching value is 10), but not "tasmania" (has the same cost)

I am currently trying this array formula:

{ =MIN(IF(B:B>=8;IF(C:C>=4;D;""));1) }

Which returns the correct lowest D, but since I am losing the informaiton about A, I can not retrieve the value for A

Upvotes: 0

Views: 46

Answers (1)

tigeravatar
tigeravatar

Reputation: 26640

This as an array formula should work for you:

=INDEX($A$1:$A$7,MATCH(MIN(IF($B$1:$B$7>=8,IF($C$1:$C$7>=4,$D$1:$D$7))),IF($B$1:$B$7>=8,IF($C$1:$C$7>=4,$D$1:$D$7)),0))

It should be noted that if you have Excel 2016 or Office365, you'll have access to the MINIFS function which is probably better suited for this task (i don't actually have the newest version, so am unable to test)

Upvotes: 1

Related Questions