Min with conditions

[gengiskhan]

Hello

thanks for all effort put in the forum success as usual also by my side.

I'm trying to understand whether possible to put in single formula this check. Basically I've got one table with several info of a product. I'd like to check that I've selected the correct Product Code where correct means the one with lowest Int.Rate between all of them, because one product can have multiple int.rate associated with it.

Of course I have not been able to upload a pic but i'm going straight with an easy excel example that i've created. Thanks for help

[Sam Capricci]

perhaps this array formula ... =MIN(IF((E7:E10=H7)*(C7:C10=G7),D7:D10)) activated by ctrl/shift/enter, adjust ranges to suit your ranges and lock cells as needed.

[gengiskhan]

well, nice to reply on a message after thousands of hours
i put the problem a little bit aside and forgot about reply to you but i came back .

actually i would argue couple of points:
- what if i'd like to get reported the product name and not the int rate ?
- what if i'd like to get fully checked that the product i'm reporting is really the only one for a given (a) product list (b) channel ? (this is a new question and change the dataset too

I'm attaching a new excel ,
thanks as usual for your deep support

[davsth]

I think you need to explain a bit further, what is the product name, there isn't a column called product name. it can be done with a match/index function
https://exceljet.net/formula/index-a...tiple-criteria

how many occurrences of a combination occur
https://exceljet.net/formula/count-u...-with-criteria

You should give us an example with what is the desired result, in your example all rows are unique

[gengiskhan]

hello to whom replied

ponit 1, it is true thta we can manage with another index/match but actually i wanted to get the min function together with then the index/match since i wanted to see the result of the min(if) with the Product code rather then min Int.Rate

accordinng with point 2 , thank you , I've modified last row of table 1. basically i would like to test then:
- i'm taking product code with lowest int rate
- i'm veryfing that i'm taking the only one existing and if not then i should see both product code

*apologies, in previous message when i was talking about product name i referred to product code

[Bo_Ry]

Please try at H7 Press Ctrl+Shift+Enter copy to I7 and copy down

=IFERROR(INDEX($D:$E,SMALL(IF($D$7:$D$19=MIN(IF($A$7:$A$19=$G$7,$D$7:$D$19)),ROW($D$7:$D$19)),ROWS(H$7:H7)),3-COLUMNS($H7:H7)),"")