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

• 13.1 KB Views: 25

NARAYANK991

Excel Ninja
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

Excel Ninja
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

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