Forum Discussion

beikme's avatar
beikme
Brass Contributor
Feb 17, 2021

copy cell

Hi all   Have small issue  I need to copy information to different cell if the number from A column is the same as in other spreadsheet  and bring all the cells in row B to another row  I tried V...
excel
Formulas and Functions
beikme's avatar
beikme
Brass Contributor
Feb 23, 2021

mathetes 

 

=FILTER('[Master Label Maker - White Goods 2.0.xlsx]Data White Goods'!$A:$B,'[Master Label Maker - White Goods 2.0.xlsx]Data White Goods'!$A:$A=E1)

  • beikme's avatar
    beikme
    Brass Contributor
    Feb 23, 2021

    mathetes 

    Currently i'm filtering betvine multiple rows  

    i would like to do it in just one row whit multiple criteria from E1,G1,I1

    • mathetes's avatar
      mathetes
      Gold Contributor
      Feb 23, 2021

      beikme 

       

      If I understand what you're saying, the formula would be this:

      =FILTER(Sheet1!A:B,(Sheet1!A:A=Sheet2!E1)+(Sheet1!A:A=Sheet2!G1)+(Sheet1!A:A=Sheet2!I1))

       

      The + sign between criteria effectively means match A:A with E1 OR G1 OR I1.

       

      If you want to get sexy, here's the same formula using the new LET function.

      =LET(

      Srce,Sheet1!A:B,

      MtSrce,Sheet1!A:A,

      FILTER(Srce,(MtSrce=Sheet2!E1)+(MtSrce=Sheet2!G1)+(MtSrce=Sheet2!I1))

      )

       

      Here's what LET does; in effect it's saying

      LET "Srce" stand in for Sheet1!A:B in the final formula

      LET "MtSrce" stand in for Shhet1!A:A in the final formula

      now do the final formula using those variables

       

      And you could make the named variables there even shorter.

      • beikme's avatar
        beikme
        Brass Contributor
        Feb 23, 2021

        mathetes 

         

        I was trying  this but it don't work 

        =FILTER(Sheet1!A:B,(IF(E1>1,Sheet1!A:A=Sheet2!E1,""))+(IF(F1>1,Sheet1!A:A=Sheet2!F1,"")))

Resources