Oct 10 2018 05:32 AM
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
Oct 10 2018 06:29 AM
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
Oct 10 2018 08:02 AM
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!
Oct 10 2018 08:19 AM
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
Oct 10 2018 08:29 AM
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.
Oct 10 2018 08:44 AM
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.