Results 1 to 4 of 4
  1. #1
    Lounger
    Join Date
    Oct 2003
    Posts
    33
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Remove Wildcard in lookup (2003)

    We have part numbers in our system that end with an "*". At the same time I have near matching part numbers that do not have the "*" I am looking to pull data via Vlookup, but the lookup on the "*" part number pulls the data from the non-asterisk p/n. I need a formula that will recognize each individual part number without the wildcard issue.

    I have attached a file to help make more sense.

    As always any help is GREATLY appreciated.

    JG
    Attached Files Attached Files

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Remove Wildcard in lookup (2003)

    You could enter a ~ before the asterisk in A3 to tell Excel to interpret it as a literal character.

    Or you could use the following array formula in B2 (confirm with Ctrl+Shift+Enter), and fill down:

    =INDEX($E$2:$E$13,MAX(ROW($1:$12)*($D$2:$D$13=A2)) )

  3. #3
    Lounger
    Join Date
    Oct 2003
    Posts
    33
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Remove Wildcard in lookup (2003)

    Thank you for all the help. It worked perfectly.

    Cheers

  4. #4
    Platinum Lounger
    Join Date
    Feb 2002
    Location
    A Magic Forest in Deepest, Darkest Kent
    Posts
    5,681
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Remove Wildcard in lookup (2003)

    JG

    Utilise the substite function like so and it will do the job

    =VLOOKUP(SUBSTITUTE(A2,"*","~"),$D$2:$E$13,2)
    Jerry

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •