Quote Originally Posted by bebo021999 View Post
Try in W10:

=IFERROR(LOOKUP(2,1/(('RB1'!$E$6:$E$1719=$D10)*(IFERROR(SEARCH("PLT",'RB1'!$J$6:$J$1719),0)+IFERROR(SEARCH("Coop",'RB1'!$J$6:$J$1719),0)+IFERROR(SEARCH("Co-op",'RB1'!$J$6:$J$1719),0)>0)),'RB1'!$BJ$6:$BJ$1719),"")
X10:

=IFERROR(LOOKUP(2,1/(('RB1'!$E$6:$E$1719=$D10)*(IFERROR(SEARCH("PLT",'RB1'!$J$6:$J$1719),0)+IFERROR(SEARCH("VB",'RB1'!$J$6:$J$1719),0)>0)),'RB1'!$BJ$6:$BJ$1719),"")
Both are confirmed by Ctrl-shift-enter.
Hey there

Thanks so much for your input.

The formula in W10 is not working properly. For example, for the first headoffice 7430624 -> if you manually filter column E in the RB1 sheet, you will see rows 1632+1634+1636+1638 state "2015 PLT COOP ADVERT", and in column BJ, it has value of 44K

The formula above is showing 0 when it should read 44K.

I did press cntrl+shift+enter

Any idea on whats not working? thank you