Lookup into Multiple Sheet for Yes/No result

MHamid

Active Member
Joined
Jan 31, 2013
Messages
385
Office Version
  1. 365
Platform
  1. Windows
  2. MacOS
Hello,

I am currently using the below formula that works, but some data has to be tweaked in order for this formula to work.
Excel Formula:
=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
Excel 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
 

Some videos you may like

Excel Facts

Round to nearest half hour?
Use =MROUND(A2,"0:30") to round to nearest half hour. Use =CEILING(A2,"0:30") to round to next half hour.

Watch MrExcel Video

Forum statistics

Threads
1,127,460
Messages
5,624,863
Members
416,063
Latest member
chaulon199

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top