1. Welcome to Chandoo.org Forums. Short message for you

    Hi Guest,

    Thanks for joining Chandoo.org forums. We are here to make you awesome in Excel. Before you post your first question, please read this short introduction guide. When posting or responding to questions please remember our values at Chandoo.org are: Humility, Passion, Fun, Awesomeness, Simplicity, Sharing Remember that we have people here for whom English is not there first language and we need to allow for this in our dealings.

    Yours,
    Chandoo
  2. 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...

  3. 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

Discussion in 'Ask an Excel Question' started by PeterL, Feb 10, 2016.

  1. PeterL

    PeterL New Member

    Messages:
    3
    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?

    Attached Files:

  2. NARAYANK991

    NARAYANK991 Excel Ninja

    Messages:
    16,619
    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
    PeterL and Thomas Kuriakose like this.
  3. shrivallabha

    shrivallabha Excel Ninja

    Messages:
    1,925
    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)
  4. r1c1

    r1c1 Administrator Staff Member

    Messages:
    191
    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 ~*.
  5. PeterL

    PeterL New Member

    Messages:
    3
    Hi Narayan,
    Thanks - Array formulas are really powerful, thanks for that idea
    KR, Peter
  6. PeterL

    PeterL New Member

    Messages:
    3
    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

Share This Page