Forum Discussion

Mark O'Mahoney's avatar
Mark O'Mahoney
Copper Contributor
Oct 10, 2018

Excel - IF function or complex V Look-up

Dear all, 

I am struggling to pull data from a cell from one tab into another tab. I am not sure if the right function is V Look-up or IF function?

The value needs to correspond with a certain country, product name, measure and year. I currently receive these files from different countries so I cannot change the layout too much. I have attached an example with dummy data to see can anyone help me. 

Thanks 

 

Formulas and Functions

5 Replies

  • SergeiBaklan's avatar
    SergeiBaklan
    Diamond Contributor
    Oct 10, 2018

    Hi Mark,

     

    That could be like

    =IFERROR(INDEX('COUNTRY X BRAND DATA SHEET'!$C$4:$F$2000,MATCH(1,INDEX(($A4='COUNTRY X BRAND DATA SHEET'!$A$4:$A$2000)*($B4='COUNTRY X BRAND DATA SHEET'!$B$4:$B$2000),1,0),0),MATCH($E4,'COUNTRY X BRAND DATA SHEET'!$C$2:$F$2,0)),"")

    and attached

    • Mark O'Mahoney's avatar
      Mark O'Mahoney
      Copper Contributor
      Oct 10, 2018

      Hi Sergei,

       

      Thank you very much for replying. 

       

      Think you have cracked it. I have added in another country and tried to drag the formula down but it is now not that simple. Is there anywhere in your formula I need to change in order to change country. 

       

      Thanks again!

      • SergeiBaklan's avatar
        SergeiBaklan
        Diamond Contributor
        Oct 10, 2018

        Hi Mark,

         

        Sorry for the misprint in formula, it shall be zero instead of 1 at the end of inner INDEX (in red here)

        =IFERROR(INDEX('COUNTRY X BRAND DATA SHEET'!$C$4:$F$2000,MATCH(1,INDEX(($A4='COUNTRY X BRAND DATA SHEET'!$A$4:$A$2000)*($B4='COUNTRY X BRAND DATA SHEET'!$B$4:$B$2000),0,0),0),MATCH($E4,'COUNTRY X BRAND DATA SHEET'!$C$2:$F$2,0)),"")

        and attached

Resources