formulas needed

[ericlch16]

Hello Experts

I have two sheets

Sheet 1
xxx123sad AAAA
dsfsdf124 BB


Sheet 2
123
124
126

I want a formula in sheet2 column B to return the corresponding value in sheet 1 if sheet 2!columnA is found in the string in sheet 1!column A.

**************************
The result should be

Sheet 2
123 AAAA
126 (empty since 126 cannot be found in sheet1!A column)
124 BB


I used sumproduct but cannot combine it with vlookup. Please help. Thank you. I would prefer not to use array if possible.

 

[Eric W]

Perhaps:

	A	B
Partial Code	Value
AAAA
BB

<colgroup><col style="width: 25pxpx"><col><col></colgroup><thead>
</thead><tbody>
[TD="align: center"]1[/TD]

[TD="align: center"]2[/TD]
[TD="align: right"]123[/TD]

[TD="align: center"]3[/TD]
[TD="align: right"]126[/TD]

[TD="align: center"]4[/TD]
[TD="align: right"]124[/TD]

</tbody>

Sheet2​

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Worksheet Formulas[TABLE="width: 100%"]
<thead>[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]
[TH="width: 10px"]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
</thead><tbody>[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]B2[/TH]
[TD="align: left"]=IFERROR(LOOKUP(2^15,SEARCH(A2,Sheet1!$A$2:$A$10),Sheet1!$B$2:$B$10),"")[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]

 

[Rick Rothstein]

VLOOKUP works, you just have to use wildcards with it...

=IFERROR(VLOOKUP("*"&A1&"*",Sheet1!$A$1:$B$2,2,FALSE),"")

 

[ericlch16]

VLOOKUP works, you just have to use wildcards with it...

=IFERROR(VLOOKUP("*"&A1&"*",Sheet1!$A$1:$B$2,2,FALSE),"")

FANTASTIC! I did not know I could use wild cards.