An alternative to the function FILTER

[Jonas375]

Hello,

I have difficulties with getting information from one array in one sheet to another array in another sheet, for the following reason :

I have one data in common between the two arrays, and I want to get all datas associated to the a data from the 1st array to the 2nd one.

Here is an excel sheet attached, with an exemple :

• I have in the first array on sheet1, column "Alley" and column "Fruits". On sheet2 I have an array, where I want to get all fruits based on the "Alley" there are in.

I've tried the function FILTER, but it does not work between two arrays. I've also tried XLOOKUP, but it only gets the first "Fruit".

Thank you for your help,

Regards, Jonas

[#2]

@Jonas375 

=INDEX(Tableau2[Fruit],SMALL(IF((Tableau2[Alley]=1)+(Tableau2[Alley]=2),ROW(Feuil1!$1:$4)),ROW(Feuil1!1:1)))

You can try this formula. Enter the formula with ctrl+shift+enter if you don't work with Office365 or 2021.

[#3]

You may use FILTER with your workbook, but the results of the formula cannot be returned within a table. A table fills formulas automatically, but is scalar-based (1 cell, 1 formula = 1 result). If you convert the table to a range in the second sheet, FILTER will work.

[#4]

@Patrick2788 

show example which work in 2013

[#5]

@vijay1960a 

I'll refer you to @OliverScheurich 's solution.