Excel minimum value within range if if meets criteria + is not found within a range

Hello everyone,

I am trying to extract data from a data set based on ID numbers. In my example, I have sold a number of various products (apple's, banana's, etc), please refer to the attached excel document.

I want to extract each sale of apple's based on their ID number, and in order to achieve that I am using this formula:
=MINIFS($B$3:$B$12;$C$3:$C$12;$J$2;$B$3:$B$12;"<>"&$I$4:I5)

The problem is that excel for some reason does not recognise the ["<>"&$I$4:I5] part.
This part makes excel check whether the ID number has been used before, and if so it should take the next minimum value (which is the next ID number of any apple sale).

For some reason, Excel gives me a 0 instead of the value that I want it to show me, and I can't figure out how to fix this.

Can someone help me out here? that would be great.

Kind regards,



PS: I hope my file got uploaded, had some issues with that in the past.

	B	C	D	E	F	G
2	ID	Sold	Name		Name	apple
3	1	100	Apple		ID	Sold
4	2	80	Banana		1	100
5	3	50	Orange		4	10
6	4	10	Apple		5	80
7	5	80	Apple		9	80
8	6	90	Orange		10	60
9	7	100	Banana
10	8	20	Orange
11	9	80	Apple
12	10	60	Apple

F4=IFERROR(INDEX(B$3:B$100,AGGREGATE(15,6,ROW(B$3:B$100)-ROW(B$3)+1/($D$3:$D$100=$G$2),ROWS($B$3:B3))),"")

copy across and down

For other fruit, type the name in G2





Copy across

Originally Posted by CARACALLA

	B	C	D	E	F	G
2	ID	Sold	Name		Name	apple
3	1	100	Apple		ID	Sold
4	2	80	Banana		1	100
5	3	50	Orange		4	10
6	4	10	Apple		5	80
7	5	80	Apple		9	80
8	6	90	Orange		10	60
9	7	100	Banana
10	8	20	Orange
11	9	80	Apple
12	10	60	Apple

F4=IFERROR(INDEX(B$3:B$100,AGGREGATE(15,6,ROW(B$3:B$100)-ROW(B$3)+1/($D$3:$D$100=$G$2),ROWS($B$3:B3))),"")

copy across and down

For other fruit, type the name in G2





Copy across

Thank you for your asnwer Caracalla,

Unfortunately your formula seems to return an error to me (the ;"" part of the IFERROR formula).
Since I do not understand what you are trying to do with this formula I cannot tell you why it doesn't work. Could you explain to me what you were trying to do or meaby give me a different formula to work with?

Kind regards,

Attached file