I'm taking a guess here that you want to match the head-office number and the PLT COOP or PLT VB with the value in RBI column BJ
On the RBI worksheet I entered this formula in column A to determine if PLT COOP or PLT VB are in RBI COLUMN J
Formula:
=IFERROR(IF(D6="","",IFERROR(IF(AND(SEARCH("*PLT*",J6),SEARCH("*COOP*",J6))=TRUE,1,""),IF(AND(SEARCH("*PLT*",J6),SEARCH("*VB*",J6))=TRUE,2,""))),"")
On OVERVIEW W10 enter this formula and fill down
Formula:
=IFERROR(INDEX('RB1'!$BJ$6:$BJ$1718,MATCH(Overview!D10&1,'RB1'!$E$6:$E$1718&'RB1'!$A$6:$A$1718,0)),"")
On OVERVIEW X10 enter this formula and fill down
Formula:
=IFERROR(INDEX('RB1'!$BJ$6:$BJ$1718,MATCH(Overview!D10&2,'RB1'!$E$6:$E$1718&'RB1'!$A$6:$A$1718,0)),"")
BOTH FORMULAE are array formulae.
...confirmed by pressing CTRL+SHIFT+ENTER to activate the array, not just ENTER. You will know the array is active when you see curly braces { } appear around your formula. If you do not CTRL+SHIFT+ENTER you will get an error or a clearly incorrect answer. Press F2 on that cell and try again.
Bookmarks