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 receivedThanks
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
Remember, saying thanks only takes a second or two. Click the star icon(*) below the post you liked, to give some Rep if you think an answer deserves it.
Please,mark your thread [SOLVED] if you received your answer.
Try
=MIN(IF(C5:C200=I1,IF(D5:D200=J1,IF(G5:G200<>0,G5:G200))))
Array formula, use Ctrl-Shift-Enter
Last edited by Special-K; 03-12-2015 at 01:14 PM.
Regards
Special-K
Ensure you describe your problem clearly, I have little time available to solve these problems and do not appreciate numerous changes to them.
yes, similar, just watch out for the blanks :D
Try:
Entered as an array.
Please Login or Register to view this content.
Spread the love, add to the Rep
"None of us are as smart as all of us."
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
If you like my answer please click on * Add Reputation
Don't forget to mark threads as "Solved" if your problem has been resolved
"Nothing is so firmly believed as what we least know."
--Michel de Montaigne
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks