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

[surkdidat]

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

 

[DanteAmor]

Try this:

varios 29jun2020.xlsm
	A	B
1
2	ABC_1000	YES
3	ABC_1001	NO
4	ABC_1002	Not exist
5
Sheet1

Cell Formulas
Range	Formula
B2:B4	B2	=IFERROR(VLOOKUP("*"&A2&"*",Sheet2!A:C,3,0),"Not exist")

varios 29jun2020.xlsm
	A	B	C
1
2	NOTIFIER_ABC_1000_XXX		YES
3	NOTIFIER_ABC_1001_XXX		NO
4
Sheet2

 

[surkdidat]

Superb - works like a gem!

Try this:

varios 29jun2020.xlsm
	A	B
1
2	ABC_1000	YES
3	ABC_1001	NO
4	ABC_1002	Not exist
5
Sheet1

Cell Formulas
Range	Formula
B2:B4	B2	=IFERROR(VLOOKUP("*"&A2&"*",Sheet2!A:C,3,0),"Not exist")

varios 29jun2020.xlsm
	A	B	C
1
2	NOTIFIER_ABC_1000_XXX		YES
3	NOTIFIER_ABC_1001_XXX		NO
4
Sheet2

 

[DanteAmor]

I'm glad to help you. Thanks for the feedback.