"*" in Formula but not as Wildcard

[JWGoldfinch]

I have some data in a column that I need to do a VLOOKUP on and the data contains an "*" Example "*AFF" and I need to find a way that I can find the exact match in another spreadsheet for "*AFF" without it using this "*" as a wildcard and returning "NBAFF" instead of "#N/A" when the value is not found.

How can I write my VLOOKUP comnmand to avoid using the "*" as a wildcard, it currently lookls like this

VLOOKUP(A2,CUSTOMERS!A:D,3,FALSE)

*AFF is not in the CUSTOMERS spreadsheet and I want it to return #N/A, instead of the name associated with NBAFF

Help Please this is a Rush Job.

 

[sheetspread]

~*

 

[Leith Ross]

Hello JWGoldfinch,

To prevent Excel from seeing the asterisk as a wildcard character, prefix it with a tilde character (~).

This example will look for what is in cell A2 that starts with an asterisk.

=VLOOKUP("~*" & A2,CUSTOMERS!A:D,3,FALSE)​

 

[JWGoldfinch]

Thank you, this helped a lot.

The "*" was not always the 1st pos, so the formula that appeared to work best for me was

VLOOKUP(SUBSTITUTE(A2,"*","~*"),'CUSTOMER'!A:D,2,FALSE)