# Extract All Values With Criteria Sorted

#### Lewzerrrr

##### Active Member
Hi,

I would like to be able to extract all values following one criteria but for the formula to automatically sort it.

Please see below table to what I'm referring to.

In table 1 would be the source data, I need to extract all the number and 4 digit letter based on the Order # & Fruit concat. but for it to then sort it within the formula.

In column L i've just used =IFERROR(INDEX(\$B\$3:\$B\$14,SMALL(IF(\$F\$3:\$F\$14=\$I3,ROW(\$B\$3:\$B\$14)-ROW(\$B\$3)+1),\$H3)),"") however this doesn't sort it as you can see in column K.

 A B C D E F G H I J K L 1 Num. 4 Digit Fruit Order # Concat Helper Concat Expected Result Expected Result INDEX SMALL IF 2 4 AAAA PEAR 2 2PEAR 1 1APPLE 7 DDDD 7 DDDD 3 5 BBBB APPLE 2 2APPLE 2 1APPLE 8 GGGG 10 FFFF 4 6 CCCC PEAR 2 2PEAR 3 1APPLE 10 FFFF 8 GGGG 5 7 DDDD APPLE 1 1APPLE 4 1APPLE 6 1 EEEE PEAR 1 1PEAR 5 1APPLE 7 10 FFFF APPLE 1 1APPLE 1 2APPLE 5 BBBB 5 BBBB 8 8 GGGG APPLE 1 1APPLE 2 2APPLE 9 12 HHHH PEAR 2 2PEAR 3 2APPLE 10 3 IIII PEAR 1 1PEAR 4 2APPLE 11 2 JJJJ PEAR 1 1PEAR 5 2APPLE 12 11 KKKK PEAR 1 1PEAR 1 1PEAR 1 EEEE 1 EEEE 13 9 LLLL PEAR 2 2PEAR 2 1PEAR 2 JJJJ 3 IIII 14 3 1PEAR 3 IIII 2 JJJJ 15 4 1PEAR 11 KKKK 11 KKKK 16 5 1PEAR 17 1 2PEAR 4 AAAA 4 AAAA 18 2 2PEAR 6 CCCC 6 CCCC 19 3 2PEAR 9 LLLL 12 HHHH 20 4 2PEAR 12 HHHH 9 LLLL

<colgroup><col span="11"><col><col></colgroup><tbody>
</tbody>

### Excel Facts

Author John Walkenbach was Mr Spreadsheet until his retirement in June 2019.

Replies
5
Views
89
Replies
4
Views
48
Replies
5
Views
44
Replies
6
Views
50
Replies
8
Views
223