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

#### surkdidat

##### Active Member
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

### 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
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
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

Replies
2
Views
391
Replies
3
Views
363
Replies
5
Views
163
Replies
18
Views
1K
Replies
3
Views
114

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.

### Which adblocker are you using?

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

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