CHOOSE() Question

[bmwbykrydr]

I have a table with two columns, an index (A) and text (B). The index numbers can change.

	A	B
1	115	Apple
2	143	Tree
3	221	Apple
4	244	Apple
5	322	Carrot
6	345	Horse
7	423	Cow
8	451	Apple

These will not always be grouped like I show them above, they will have other text values randomly mixed in and the order can change as can the index.

I need the smallest index of all of the "Apples" in a cell, then down one the next smallest. There will be 4 or less values for Apple, never more than 4.

115

221

244

451

I can't seem to get the formula's to account for changing values in either column and always choose the lowest value, then the next lowest, etc.

Don

 

[RoryA]

=SMALL(IF($B$1:$B$8="Apple",$A$1:$A$8),ROWS($A$1:$A1))

array entered with Ctrl+Shift+Enter, then fill down another 3 cells.

 

[BSALV]

2 solutions

Map1
	A	B	C	D	E	F	G	H
1	A	B			451		451	Apple
2	1	115	Apple		244		244	Apple
3	2	143	Tree		221		221	Apple
4	3	221	Apple		no more		115	Apple
5	4	244	Apple		no more
6	5	322	Carrot		no more
7	6	345	Horse		no more
8	7	423	Cow		no more
9	8	451	Apple		no more
10
Blad1

Cell Formulas
Range	Formula
G1:H4	G1	=SORT(FILTER(B2:C9,C2:C9="apple"),1,-1)
E1:E9	E1	=IFERROR(AGGREGATE(14,6,B2:B9/(C2:C9="apple"),ROW()),"no more")
Dynamic array formulas.