INDEX, MATCH and MIN functions

[cheeky300]

HI guys, would appreciate some help. I am trying to lookup two separate criteria and return the minimum price. I've attached a spreadsheet as an example. I've tried INDEX, MATCH and MATCH before I add in the MIN function but it doesn't seem to work. Your advice would be greatly received Thanks

[GeneralDisarray]

Try this array function:

=MIN(IF(($D$5:$D$156=J1)*($C$5:$C$156=I1)*($G$5:$G$156<>"")=1,$G$5:$G$156,10^6))

After you place the function in the cell, press cntrl+shift+enter

[Special-K]

Try

=MIN(IF(C5:C200=I1,IF(D5:D200=J1,IF(G5:G200<>0,G5:G200))))

Array formula, use Ctrl-Shift-Enter

[GeneralDisarray]

yes, similar, just watch out for the blanks :D

[mcmahobt]

Try:

Please Login or Register  to view this content.

Entered as an array.

[AlKey]

Array entered

=SMALL(IF((C5:C156=I1)*(D5:D156=J1)*(G5:G156<>""),G5:G156),1)

***Array formula must be entered by using key combination of CTRL+SHIFT+ENTER and not just ENTER