Forum Discussion
Excel - IF function or complex V Look-up
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!
- SergeiBaklanOct 10, 2018Diamond Contributor
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
- Mark O'MahoneyOct 10, 2018Copper Contributor
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.
- SergeiBaklanOct 10, 2018Diamond Contributor
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.