Forum Discussion

Michael Kønig's avatar
Michael Kønig
Copper Contributor
Sep 25, 2018

I need help with a formula collecting data from one sheet to another

 Hi Communitie
I hope that you can help me.

 

In a file with two sheets i have column C with costumer names and numbers in both sheets.

In sheet A I also have their turnover.

In sheet B (besides name and number) I also have their country of origin.

 

Costumers in sheet A will always be in sheet B col.C but costumers in sheet B col. C may not nessecealy be in sheet A Col.C.

Customers in the name column could be repeated but with individual numbers (entered twice or more) but country of origin will be the same no matter the numbers.


The order of the costumers name is not the same in the two col. and can not be sorted to be the same.

 

Question: What formular schould I use in a new col. in sheet A to drag the country of origin to mach the costumer name in sheet A col.C, given that when a costumer name in sheet A colC match a costumer name in sheet B col.C it will return the value of the cell: Country for the given name/value in sheetB col.C.

Pleas ask if You don´t know what I mean

Thanks for your help BR MIK

excel
Formulas and Functions
  • Philip West's avatar
    Philip West
    Steel Contributor
    Sep 25, 2018

    Hia,

    this will work:

    =INDEX('Ark2'!G:G,MATCH('Ark1'!C13,'Ark2'!C:C,0))

     

    I've assumed that you get this report given to you and your want something you can just add and know it will work without any additional work? Which is why its using whole columns.

    • Michael Kønig's avatar
      Michael Kønig
      Copper Contributor
      Sep 25, 2018

      Hi Philip

       

      Thanks your response:
      I can see the whole idea in the formula and despite the fact that I have to translate INDEX and MATC to the danish version I still can´t get the result I want.

       

      Are you shure that the last zero is correct ?? I can´t see the refernce for that ?

      • Philip West's avatar
        Philip West
        Steel Contributor
        Sep 25, 2018

        https://en.excel-translator.de/translator/

        I'm not sure about the comer/semicolon options, but its a good tool.

         

        The 0 specifies the type of match we want, in this case 0 is an exact match which is what we want.

         

        I'm uploading a copy, of your workbook which works for me, not sure if that will help given the localization.

         

        Also just noticed the formula should have been:

        =INDEX('Ark2'!G:G,MATCH('Ark1'!C7,'Ark2'!C:C,0))

        Not C13 in the first one. Sorry, that might be whats messing it up for you.

Resources