VLOOKUP and cells with "*" NOT to be interpreted as wildcard

[PeterL]

I have a challenge with VLOOKUP: I get a lot of data out of an ERP system which in one of the fields has one or more "*" as the first characters (See sample B4:C13).
In order to classify the many records I have a table for lookup ( F4:F15) and I do VLOOKUP (Col A) to find the right "Monthly Report Item".
Unfortunately it seems like VLOOKUP interprets the "*" as a wildcard and does not find the wanted value: A5:B5 does not provide the correct answer!
For test, I have in rows 17 and below replaced the "*" with "+" in all places and everything works fine.
I would like to avoid doing such a replacement in the downloaded data. Is there any way to fix this? I would also like to avoid using VBA - some REGEX could probably fix this, but how to implement in a formula?

 

[NARAYANK991]

Hi Peter ,

You can do the REPLACE within the formula , as follows :

=VLOOKUP(SUBSTITUTE(B4,"*","|"),SUBSTITUTE($F$4:$G$15,"*","|"),2,FALSE)

This is an array formula , to be entered using CTRL SHIFT ENTER.

Narayan

 

[shrivallabha]

Following also seems to be working correctly (Non-Array). Normally entered:
=LOOKUP(2,FIND(B4,$F$4:$F$15),$G$4:$G$15)

Edit: following approach should make it better
=LOOKUP(2,1/(B4=$F$4:$F$15),$G$4:$G$15)

 

[r1c1]

Another alternative, probably the supposed way to do this.

=VLOOKUP(SUBSTITUTE(B4,"*","~*"),$F$4:$G$15,2,FALSE)

Essentially, to bypass the wild cards, you need to use ~. Since your original lookup values are already in the cells, you can use SUBSTITUTE to replace the *s with ~*.

 

[PeterL]

Hi Peter ,

You can do the REPLACE within the formula , as follows :

=VLOOKUP(SUBSTITUTE(B4,"*","|"),SUBSTITUTE($F$4:$G$15,"*","|"),2,FALSE)

This is an array formula , to be entered using CTRL SHIFT ENTER.

Narayan

Hi Narayan,
Thanks - Array formulas are really powerful, thanks for that idea
KR, Peter

 

[PeterL]

Another alternative, probably the supposed way to do this.

=VLOOKUP(SUBSTITUTE(B4,"*","~*"),$F$4:$G$15,2,FALSE)

Essentially, to bypass the wild cards, you need to use ~. Since your original lookup values are already in the cells, you can use SUBSTITUTE to replace the *s with ~*.

r1c1, this is great solution - so simple! J
I do prefer this one as others who will be using the spreadsheet might run into trouble with other solutions based on array formulas.
Thanks!
Peter