Lookup-and-return-multiple-values(Result:Ascending)

[chief_abound]

Lookup-and-return-multiple-values(Result:Ascending)
Snap6.jpg

[Glenn Kennedy]

I was about to moan about the lack of a sheet. However, you've fixed that!!
For the country:

Please Login or Register  to view this content.

For the item:

Please Login or Register  to view this content.

Both are array formulae. Array Formulae are a little different from ordinary formulas in that they MUST be confirmed by pressing CTRL+SHIFT+ENTER to activate the array, not just ENTER.

You will know the array is active when you see curly braces { } appear around the outside of your formula. If you do not CTRL+SHIFT+ENTER you will (almost always) get an error message or an incorrect answer. Press F2 on that cell and try again.

Don't type the curly braces yourself - it won't work...

[Tony Valko]

Try this...

Data Range

	G	H
4	Sort order :	Items :
5	2	C
6	2	I
7	12	A
8	15	D
9	18	H
10

This array formula** entered in G5:

=IFERROR(SMALL(IF(A$2:A$9=H$2,B$2:B$9),ROWS(G$5:G5)),"")

This array formula** entered in H5:

=IF(G5="","",INDEX(C:C,SMALL(IF((A$2:A$9=H$2)*(B$2:B$9=G5),ROW(B$2:B$9)),COUNTIF(G$5:G5,G5))))

** array formulas need to be entered using the key
combination of CTRL,SHIFT,ENTER (not just ENTER).
Hold down both the CTRL key and the SHIFT key
then hit ENTER.

Select G5:H5 and copy down until you get blanks.

[chief_abound]

Hi! Glenn Kennedy

As my computer had some problem ,file was not attached properly , Sorry for
causing you inconvenience. Anyway, Thank you so much for good answers.

[chief_abound]

Hi! Tony Valko

I am really amazed with your answers every time, your answers improved my study a lot. Thank you so much!

[Tony Valko]

You're welcome. Thanks for the feedback!