Forum Discussion

Larsvane's avatar
Larsvane
Copper Contributor
Mar 27, 2023

How to copy a rowdata when matching a value in a column to another worksheet

Hello,   I am looking for help with an excelsheet I am putting together. I have data differences that are presented in column A of worksheet 1: Raw Material and Finished product. In the next couple...
excel
Formulas and Functions
office 365
OliverScheurich's avatar
OliverScheurich
Gold Contributor
Mar 27, 2023

Larsvane 

=IFERROR(INDEX(A$2:A$9,SMALL(IF($A$2:$A$9="Raw material",ROW($A$2:$A$9)-1),ROW($A1))),"")

You can try this formula. Enter the formula with ctrl+shift+enter if you don't work with Office 365 or Excel 2021. The formula is in cell F2 in the example and filled across range F2:I8.

 

If you work with Office 365 you can apply the FILTER function.

 

Another alternative that works for older versions of Excel could be advanced filter.

Larsvane's avatar
Larsvane
Copper Contributor
Mar 27, 2023
Thanks for your reply. Okey I see how this works, but it does not work in another worksheet (after changing the values A2:A9 to worksheet!A2:a9) How can I incoperate this?
  • OliverScheurich's avatar
    OliverScheurich
    Gold Contributor
    Mar 27, 2023

    Larsvane 

    =IFERROR(INDEX(worksheet!A$2:A$9,SMALL(IF(worksheet!$A$2:$A$9="Raw material",ROW(worksheet!$A$2:$A$9)-1),ROW(worksheet!$A1))),"")

    This formula works in my file. I have to confirm the formula with ctrl+shift+enter because the attached file is version Excel 2013. Does it work when you open the attached file?

     

    • Larsvane's avatar
      Larsvane
      Copper Contributor
      Apr 13, 2023

      OliverScheurich Thanks for the reply. I tried it in your file and it worked. Then I tried the exact same in mine and it did not work fully. I attached part of the file i am using for it. In my case it should use the array of d4-d10. It also does not work in "blad 2"

Resources