Forum Discussion

Jonas375's avatar
Jonas375
Copper Contributor
Oct 27, 2022

An alternative to the function FILTER

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

excel
  • OliverScheurich's avatar
    OliverScheurich
    Gold Contributor
    Oct 27, 2022

    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.

  • Patrick2788's avatar
    Patrick2788
    Silver Contributor
    Oct 27, 2022
    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.

Resources