Regarding getting the smallest value into a specific item.

[jcc12345]

Good day! I'm having a hard time thinking for this formula. I tried to make an aggregate formula for this and also tried SMALL + VLOOKUP formula. Please see attached file for the full description. Thank you so much! :D

[azumi]

=MIN(IF(A2:A6="Apple",B2:B6))

Array Formula, you need to press CTRL-SHIFT-ENTER button together, not ENTER alone

Thanks

[jcc12345]

Thanks very much Azumi! But can we add INDEX function to it so that we don't have to press CTRL + SHIFT + ENTER? :D

[azumi]

Maybe, but not found it yet

[vlady]

like this?
can't remember where i got it. thanks to who ever she/he was.

Please Login or Register  to view this content.

[XOR LX]

Hi,

What version of Excel are you using?

Regards

[azumi]

like this?
can't remember where i got it. thanks to who ever she/he was.

Please Login or Register  to view this content.

Nice and rare formula vlady.... cheers

[humdingaling]

Please Login or Register  to view this content.

[XOR LX]

Please Login or Register  to view this content.

For a start, that needs to be array-entered. Secondly, and more importantly, it simply doesn't work (unless all the entries in A2:A6 are Apple!).

Regards

[humdingaling]

i got the right answer by accident

i have no idea why it worked on my sheet until i tried it on a different row than row 5

[humdingaling]

Please Login or Register  to view this content.

tested to work for this specific example

[XOR LX]

Please Login or Register  to view this content.

tested to work for this specific example

That's better! And the D-functions are sadly often underused in Excel.

Regards

[XOR LX]

Nice and rare formula vlady.... cheers

But doesn't it fail if there is a negative value in the range which isn't associated with "Apple"?

Regards

[humdingaling]

Thanks

Dmin continues to work in the case of negatives

only "thing" with the D functions is the need to use headers

[vlady]

Another one.

Please Login or Register  to view this content.

[Tony Valko]

If you're using Excel 2010 or later...

Data Range

	A	B	C	D	E
1	Fruit	Sales	------	Fruit	Min Sales
2	Apple	120		Apple	100
3	Banana	80
4	Apple	180
5	Apple	100
6	Banana	170

=AGGREGATE(15,6,B2:B6/(A2:A6=D2),1)