look up text with closest match

[bizpioneer]

I've been trying to look up the closest match in excel.. for example:

sheet1 in column A:
Red carpets
blue cheese


Sheet2 in Column A:
Fast carpets
Free carpets
Red carpets for free
blue pen

is there a formula that looks up the closest match in this case...
"Red carpet" in sheet 1 should match up with "red carpets for free"
and "blue cheese" in sheet 1 should be matched with "blue pen" in sheet 2, etc

 

[MARZIOTULLIO]

	A​	B​	C​	D​	E​
1​	Red carpets		Red carpets for free		sheet1
2​	blue cheese

<tbody>
</tbody>

	A​	B​	C​
1​	Fast carpets		sheet2
2​	Free carpets
3​	Red carpets for free
4​	blue pen

<tbody>
</tbody>

sheet 1

c1=VLOOKUP(A1&"*",Sheet2!$A$1:$A$4,1,0)

 

[bizpioneer]

	A​	B​	C​	D​	E​
1​	Red carpets		Red carpets for free		sheet1
2​	blue cheese

<tbody>
</tbody>

	A​	B​	C​
1​	Fast carpets		sheet2
2​	Free carpets
3​	Red carpets for free
4​	blue pen

<tbody>
</tbody>

sheet 1

c1=VLOOKUP(A1&"*",Sheet2!$A$1:$A$4,1,0)

This is almost it, but it doesn't work if there is a phrase before or after "Red Carpet" in sheet 1 A1

 

[MARZIOTULLIO]

his is almost it, but it doesn't work if there is a phrase before or after "Red Carpet" in sheet 1 A1: for example ?

 

[MARZIOTULLIO]

This is almost it, but it doesn't work if there is a phrase before or after "Red Carpet" in sheet 1 A1 =for example?

try

sheet1

c1=VLOOKUP("*"&A1&"*",Sheet2!$A$1:$A$4,1,0)

 

[bizpioneer]

This is almost it, but it doesn't work if there is a phrase before or after "Red Carpet" in sheet 1 A1

try

sheet1

c1=VLOOKUP("*"&A1&"*",Sheet2!$A$1:$A$4,1,0)

Still no go

 

[MARZIOTULLIO]

please a example afer "red carpet"

 

[bizpioneer]

	A​	B​	C​	D​	E​
1​	Red carpets		Red carpets for free		sheet1
2​	amazing red carpets		#NA

<tbody>
</tbody>

	A​	B​	C​
1​	Fast carpets		sheet2
2​	Free carpets
3​	Red carpets for free
4​	blue pen

<tbody>
</tbody>