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]

[TABLE="class: grid"]
<tbody>[TR]
[TD][/TD]
[TD]

A​

[/TD]
[TD]

B​

[/TD]
[TD]

C​

[/TD]
[TD]

D​

[/TD]
[TD]

E​

[/TD]
[/TR]
[TR]
[TD]

1​

[/TD]
[TD]Red carpets[/TD]
[TD][/TD]
[TD]Red carpets for free[/TD]
[TD][/TD]
[TD]sheet1[/TD]
[/TR]
[TR]
[TD]

2​

[/TD]
[TD]blue cheese[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]




[TABLE="class: grid"]
<tbody>[TR]
[TD][/TD]
[TD]

A​

[/TD]
[TD]

B​

[/TD]
[TD]

C​

[/TD]
[/TR]
[TR]
[TD]

1​

[/TD]
[TD]Fast carpets[/TD]
[TD][/TD]
[TD]sheet2[/TD]
[/TR]
[TR]
[TD]

2​

[/TD]
[TD]Free carpets[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]

3​

[/TD]
[TD]Red carpets for free[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]

4​

[/TD]
[TD]blue pen[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

sheet 1

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

 

[bizpioneer]

[TABLE="class: grid"]
<tbody>[TR]
[TD][/TD]
[TD]

A​

[/TD]
[TD]

B​

[/TD]
[TD]

C​

[/TD]
[TD]

D​

[/TD]
[TD]

E​

[/TD]
[/TR]
[TR]
[TD]

1​

[/TD]
[TD]Red carpets[/TD]
[TD][/TD]
[TD]Red carpets for free[/TD]
[TD][/TD]
[TD]sheet1[/TD]
[/TR]
[TR]
[TD]

2​

[/TD]
[TD]blue cheese[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]




[TABLE="class: grid"]
<tbody>[TR]
[TD][/TD]
[TD]

A​

[/TD]
[TD]

B​

[/TD]
[TD]

C​

[/TD]
[/TR]
[TR]
[TD]

1​

[/TD]
[TD]Fast carpets[/TD]
[TD][/TD]
[TD]sheet2[/TD]
[/TR]
[TR]
[TD]

2​

[/TD]
[TD]Free carpets[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]

3​

[/TD]
[TD]Red carpets for free[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]

4​

[/TD]
[TD]blue pen[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

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]

[TABLE="class: cms_table_grid"]
<tbody>[TR]
[TD][/TD]
[TD]

A​

[/TD]
[TD]

B​

[/TD]
[TD]

C​

[/TD]
[TD]

D​

[/TD]
[TD]

E​

[/TD]
[/TR]
[TR]
[TD]

1​

[/TD]
[TD]Red carpets[/TD]
[TD][/TD]
[TD]Red carpets for free[/TD]
[TD][/TD]
[TD]sheet1[/TD]
[/TR]
[TR]
[TD]

2​

[/TD]
[TD]amazing red carpets[/TD]
[TD][/TD]
[TD]#NA[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]





[TABLE="class: cms_table_grid"]
<tbody>[TR]
[TD][/TD]
[TD]

A​

[/TD]
[TD]

B​

[/TD]
[TD]

C​

[/TD]
[/TR]
[TR]
[TD]

1​

[/TD]
[TD]Fast carpets[/TD]
[TD][/TD]
[TD]sheet2[/TD]
[/TR]
[TR]
[TD]

2​

[/TD]
[TD]Free carpets[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]

3​

[/TD]
[TD]Red carpets for free[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]

4​

[/TD]
[TD]blue pen[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]