Lookup into Multiple Sheet for Yes/No result

[MHamid]

Hello,

I am currently using the below formula that works, but some data has to be tweaked in order for this formula to work.

=IF($AB7<>"",UPPER(VLOOKUP($L7,Check_YTD!$B:$T,19,0)),IF(SUMPRODUCT(--(ISNUMBER(SEARCH("- "&CBNA_LVID,BC7))))>0,"YES",IF($AB7="",VLOOKUP($L7,Prelim_AuditPlan!$B:$K,10,0),"NO")))

I need to have a YES or NO result.
The first part of the formula is looking at a sheet (Check_YTD) for an account number. If the account number exists, then i want the data from column T to be brought over [NO ISSUE WITH THIS PART].
The second part of the formula looks at a list of business codes from another sheet (CBNA) and will compare the list of codes in that sheet to the cell string in column BC [JUST FINISHED GETTING THIS WORK, BUT THIS IS THE PIECE THAT I AM LOOKING TO NOT TWEAK WITHIN THE CBNA SHEET].
The third part of the formula looks to see if the account number is listed in a third sheet (Prelim_AuditPlan). If the account number exists, then i want the data from column K to be brought over [NO ISSUE WITH THIS PART].
The last part of the formula will simply say "NO" if the first three parts of the formula are false.

What i actually need in the second part of the formula

IF(SUMPRODUCT(--(ISNUMBER(SEARCH("- "&CBNA_LVID,BC7))))>0,"YES"

is to look at the CBNA sheet without it being tweaked. When I say tweaked I mean that the data comes with data from columns A-N. The codes are in column G and the YES/NO I need to be brought over are in column N. I need to do a lookup of the string of codes in the main data (cell BC) and compare those codes to the list of codes in the CBNA Sheet. If the code exists in the CBNA Sheet, then bring back the YES or NO from column N. Since I couldn't figure this out, I ended up just removing all of the NO codes from the CBNA Sheet, but I don't want to do that. I want to do the lookup to bring over the Yes or No from column N.

Any ideas how I can update the formula to do what I actually need, which is to compare the codes and bring over the Yes or No from column N from the CBNA Sheet?


Thank you