Forum Discussion

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 coun...

Excel Example.xlsx13 KB

Formulas and Functions

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

Excel Example (2).xlsx13 KB

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!

Excel Example (2).xlsx14 KB

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
Excel Example (2) (1).xlsx14 KB
- Mark O'Mahoney
  Copper Contributor
  Oct 10, 2018
  Hi Sergei,
  
  Thank you very much for emailing me back and for helping me with the formula.
  
  I am new to complex IF functions like this so I will be very much studying your formula over the next few days. If I have any other formula questions would you be willing to help me again?
  
  Many thanks,
  Mark.
  - SergeiBaklan
    Diamond Contributor
    Oct 10, 2018
    Hi Mark,
    
    Yes, sure, however don't promise immediate answer.
    
    If you are not familiar with INDEX/MATCH technique please check this https://exceljet.net/index-and-match, and google will find you lot more information.