MIN function

[Ogniani]

Hello, I have a large database with two criteria (offers and product type). The aim is to find which offer contains the most proposals with a minimum price (e.g. row 8, column F,G,H). The manual solution is not possible due to the large number of rows (product type), so I hope for an automatic solution.

I have already tried the following functions, but I am obviously mistaken because they do not give the correct results:
=MINIFS(B3:D3,B2:D2,B2,A3:A6,A3)

array formulas:
{=MIN(IF(B2:D2=B2,IF(A3:A6=A3,B3:D3)))}
{=MIN(IF(B2:D2=B2,B3:D3))}

Is there any solution or not possible through excel?

 

[jasonb75]

Based on your screen capture, enter this into F3 and drag right / fill down.

=MINIFS($B3:$D3,$B$2:$D$2,B$2)

 

[Fluff]

What version of Excel are you using?

I suggest that you update your Account details (or click your user name at the top right of the forum) so helpers always know what Excel version(s) & platform(s) you are using as the best solution often varies by version. (Don’t forget to scroll down & ‘Save’)

Another possible solution

+Fluff 1.xlsm
	A	B	C	D	E	F	G	H
1
2		Offer 1	Offer 2	Offer 3		Offer 1	Offer 2	Offer 3
3	a	15	25	21		15	0	0
4	b	13	20	10		0	0	10
5	c	35	23	30		0	23	0
6	d	5	7	1		0	0	1
Main

Cell Formulas
Range	Formula
F3:H6	F3	=IF(MINIFS($B3:$D3,$B$2:$D$2,F$2)=MIN($B3:$D3),MIN($B3:$D3),0)

 

[jasonb75]

Another possible solution

Actually, the only solution. I totally messed mine up

 

[Ogniani]

Based on your screen capture, enter this into F3 and drag right / fill down.

=MINIFS($B3:$D3,$B$2:$D$2,B$2)

Hmm thank you, but it seems that there is some problem - #NAME?
Neither if I try to made it array formula... so do you have an idea what could it be?

 

[jasonb75]

so do you have an idea what could it be?

It means that your version of excel is too old for MINIFS. Please update your account details as @Fluff has suggested so that we can provide more suitable alternatives.

 

[Ogniani]

What version of Excel are you using?

I suggest that you update your Account details (or click your user name at the top right of the forum) so helpers always know what Excel version(s) & platform(s) you are using as the best solution often varies by version. (Don’t forget to scroll down & ‘Save’)

Another possible solution

+Fluff 1.xlsm
	A	B	C	D	E	F	G	H
1
2		Offer 1	Offer 2	Offer 3		Offer 1	Offer 2	Offer 3
3	a	15	25	21		15	0	0
4	b	13	20	10		0	0	10
5	c	35	23	30		0	23	0
6	d	5	7	1		0	0	1
Main

Cell Formulas
Range	Formula
F3:H6	F3	=IF(MINIFS($B3:$D3,$B$2:$D$2,F$2)=MIN($B3:$D3),MIN($B3:$D3),0)

Hi, thank you too.
Again, there is an error in the cell, so as I understand the problem is it form Excel version (mine is 2016, I update it, thank you for that too).

In this case is there a solution or only option is to update the Excel?

 

[jasonb75]

Here's @Fluff's suggestion edited to work with 2016. Note that you will need to array confirm the formula for it to work.

Book1
	A	B	C	D	E	F	G	H
1
2		Offer 1	Offer 2	Offer 3		Offer 1	Offer 2	Offer 3
3	a	15	25	21		15	0	0
4	b	13	20	10		0	0	10
5	c	35	23	30		0	23	0
6	d	5	7	1		0	0	1
Sheet1

Cell Formulas
Range	Formula
F3:H6	F3	=IF(MIN(IF($B$2:$D$2=F$2,$B3:$D3))=MIN($B3:$D3),MIN($B3:$D3),0)

 

[Fluff]

Thanks for that, how about

+Fluff 1.xlsm
	A	B	C	D	E	F	G	H
1
2		Offer 1	Offer 2	Offer 3		Offer 1	Offer 2	Offer 3
3	a	15	25	21		15	0	0
4	b	13	20	10		0	0	10
5	c	35	23	30		0	23	0
6	d	5	7	1		0	0	1
Main

Cell Formulas
Range	Formula
F3:H6	F3	=IF(MIN(IF($B$2:$D$2=F$2,$B3:$D3))=MIN($B3:$D3),MIN($B3:$D3),0)
Press CTRL+SHIFT+ENTER to enter array formulas.

 

[Ogniani]

Thanks for that, how about

+Fluff 1.xlsm
	A	B	C	D	E	F	G	H
1
2		Offer 1	Offer 2	Offer 3		Offer 1	Offer 2	Offer 3
3	a	15	25	21		15	0	0
4	b	13	20	10		0	0	10
5	c	35	23	30		0	23	0
6	d	5	7	1		0	0	1
Main

Cell Formulas
Range	Formula
F3:H6	F3	=IF(MIN(IF($B$2:$D$2=F$2,$B3:$D3))=MIN($B3:$D3),MIN($B3:$D3),0)
Press CTRL+SHIFT+ENTER to enter array formulas.

Thanks to both of you, now everything is working perfect, just great!