VLOOKUP the * symbol

[rossi789]

Years ago, my business decided to prefix products to quickly identify a products "Status". Unfortunately they decided to use a *, which of course in excel denotes a wildcard character.

I am trying to vlookup a table to quickly translate the prefixes but those with a * do not lookup correctly.

Lookup Table A1:B8:

Symbol	Status
*	Bespoke
#	New
#*	New Bespoke
+	To be delisted
**	Delisted
+*	To be delisted bespoke
+#	To be delisted New

<colgroup><col><col></colgroup><tbody>
</tbody>

Formula Test D2:E8

*	Bespoke
#	New
#*	New
+	To be delisted
**	Bespoke
+*	To be delisted
+#	To be delisted New

<colgroup><col><col></colgroup><tbody>
</tbody>

Formula: =VLOOKUP(D2,$A$2:$B$8,2,FALSE)

 

[RoryA]

Escape it with a tilde:

=VLOOKUP(substitute(D2,"*","~*"),$A$2:$B$8,2,FALSE)

 

[rossi789]

Escape it with a tilde:

=VLOOKUP(substitute(D2,"*","~*"),$A$2:$B$8,2,FALSE)

Worked perfectly, thank you!