Search code examples
excelexcel-formulaarray-formulas

Excel Multiple value autofill

I am trying to find the excel formula where in Sheet2!A2 (Here-3) is filled any from Sheet1!A, it will give all values from Sheet1!B in Sheet2!B.

enter image description here

Thanks & Regards, Rakib

Solution

  • What you need to filter data based on condition. Following formula will filter data on same sheet.

    =IFERROR(INDEX($B$1:$B$7,AGGREGATE(15,6,ROW($A$1:$A$7)/($A$1:$A$7=$D$2),ROW(1:1))),"")

    enter image description here

    For Sheet2 use following formula.

     =IFERROR(INDEX(Sheet1!$B$1:$B$7,AGGREGATE(15,6,ROW(Sheet1!$A$1:$A$7)/(Sheet1!$A$1:$A$7=$A$1),ROW(1:1))),"")

    enter image description here