INDEX to return multiple value

[xatomicx]

Hi,

I've been able to do this before using a Index function ... but i lost how to do it, and what i find online is not really what i need.

what i want is;

if column A = Sondages , then return me the value in column B. There will be multiple results.. (wich is why i need to use index instead of a simple vlookup) so i need to stack the result on top of each other in column G1 down.

i know it's a combination of index, match & stuff.

Thanks for helping

[nigelbloomy]

Does this help in C2?

Please Login or Register  to view this content.

[xatomicx]

PArtially works, sometimes it returns me twice or more the same value, sometimes it doesnt.

[nigelbloomy]

Can you upload a sample of your spreadsheet without any sensitive data? Click on "Go Advanced" and then use the paperclip icon to attach the file.

The first countif is looking at all of the results already given in the unique list. You shouldn't be able to get more than one unique value returned. But if you attach an example file we can look and see what is going on.

[xatomicx]

Was able to complete with an array formula .. thanks !

[nigelbloomy]

Good. I'm glad you found a way to do what you needed.

[oeldere]

@xatomicx

can you post the array formula, for the benefit of other members?

[xatomicx]

Sure

Please Login or Register  to view this content.

Then i just drag down the cell until its done.

[Tony Valko]

Try this...

Data Range

	A	B	C	D
1
2	Apples	Data1	Sondages	Data2
3	Sondages	Data2		Data4
4	Grapes	Data3		Data7
5	Sondages	Data4		Data8
6	Fish	Data5
7	Sponges	Data6
8	Sondages	Data7
9	Sondages	Data8
10	Tulips	Data9
11

This array formula** entered in D2:

=IFERROR(INDEX(B:B,SMALL(IF(A$2:A$10=C$2,ROW(A$2:A$10)),ROWS(D$2:D2))),"")

** 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.

Copy down until you get blanks.