• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

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

PeterL

New Member
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?
 

Attachments

r1c1

Administrator
Staff member
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

New Member
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

New Member
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
 
Top