Return value of Column C (in sheet2) if whole contents of a1 (sheet1 is found in Column A(sheet 2)

surkdidat

Active Member
Joined
Oct 1, 2011
Messages
369
Office Version
  1. 2016
Hopefully the title basically explains it!

In Sheet 1 in Column A I have several different references such as ABC_1000, ABC_1001, ABC_1002 etc.

In Sheet2 in Column A will be text such as NOTIFIER_ABC_1000_XXX, NOTIFIER_ABC_1001_XXX etc

In sheet2, in Column C will contain a YES or NO

I just need a formula in Sheet 1 (Column B) that will look at the shortened code in ColumnA, Sheet 1, and find if its a YES or NO in Sheet2 in the long name

Many thanks
 

Some videos you may like

Excel Facts

Get help while writing formula
Click the italics "fx" icon to the left of the formula bar to open the Functions Arguments dialog. Help is displayed for each argument.

DanteAmor

Well-known Member
Joined
Dec 3, 2018
Messages
12,619
Office Version
  1. 2007
Platform
  1. Windows
Try this:
varios 29jun2020.xlsm
AB
1
2ABC_1000YES
3ABC_1001NO
4ABC_1002Not exist
5
Sheet1
Cell Formulas
RangeFormula
B2:B4B2=IFERROR(VLOOKUP("*"&A2&"*",Sheet2!A:C,3,0),"Not exist")


varios 29jun2020.xlsm
ABC
1
2NOTIFIER_ABC_1000_XXXYES
3NOTIFIER_ABC_1001_XXXNO
4
Sheet2
 

surkdidat

Active Member
Joined
Oct 1, 2011
Messages
369
Office Version
  1. 2016
Superb - works like a gem!

Try this:
varios 29jun2020.xlsm
AB
1
2ABC_1000YES
3ABC_1001NO
4ABC_1002Not exist
5
Sheet1
Cell Formulas
RangeFormula
B2:B4B2=IFERROR(VLOOKUP("*"&A2&"*",Sheet2!A:C,3,0),"Not exist")


varios 29jun2020.xlsm
ABC
1
2NOTIFIER_ABC_1000_XXXYES
3NOTIFIER_ABC_1001_XXXNO
4
Sheet2
 

DanteAmor

Well-known Member
Joined
Dec 3, 2018
Messages
12,619
Office Version
  1. 2007
Platform
  1. Windows
I'm glad to help you. Thanks for the feedback.
 

Watch MrExcel Video

Forum statistics

Threads
1,127,556
Messages
5,625,498
Members
416,113
Latest member
Zulwaqar88

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