Excel - IF function or complex V Look-up

Copper Contributor

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 

 

5 Replies

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

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!

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

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. 

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.