+ Reply to Thread
Results 1 to 7 of 7

Vlookup formula not working

  1. #1
    Forum Contributor
    Join Date
    04-09-2013
    Location
    india
    MS-Off Ver
    Excel 2010
    Posts
    156

    Vlookup formula not working

    Hi to all
    Good Afternoon
    I have data like this
    CR86404*7*2*1*1*1*1*1*1R/CR24]*1*1*1*1 IN-18W-5-01210
    CR86404*7*2*1*1*1*1*1*1 IN-18W-5-02549

    in another sheet or column i am trying to IN code vlookup for these two
    CR86404*7*2*1*1*1*1*1*1R/CR24]*1*1*1*1
    CR86404*7*2*1*1*1*1*1*1

    but i am getting same code only IN-18W-5-01210 for both the two entries but i wish to get like this
    CR86404*7*2*1*1*1*1*1*1R/CR24]*1*1*1*1 IN-18W-5-01210
    CR86404*7*2*1*1*1*1*1*1 IN-18W-5-02549

    I do not know why vlookup is giving this type of results i tried combination of vlookup and substitute functions still no success. I really need help to solve this.
    any help in this regard is highly appreciable. Please find attached file for better understanding
    Thanks in advance
    Regards,
    Attached Files Attached Files

  2. #2
    Forum Expert
    Join Date
    06-08-2012
    Location
    Left the forum!
    MS-Off Ver
    Left the forum!
    Posts
    5,189

    Re: Vlookup formula not working

    The * character in the lookup ctiteria is used as a wildcard character, you need to cancel the wildcard functionality to make it work.

    =VLOOKUP(SUBSTITUTE(A7,"*","~*"),$A$2:$B$3,2,FALSE)

  3. #3
    Forum Moderator Richard Buttrey's Avatar
    Join Date
    01-14-2008
    Location
    Stockton Heath, Cheshire, UK
    MS-Off Ver
    Office 365, Excel for Windows 2010 & Excel for Mac
    Posts
    28,420

    Re: Vlookup formula not working

    It's the asterisks which are acting as a wild card that prvent the VLOOKUP working as you expect. Yuo need to substitite the * for a ~

    So in B8 for example use

    Formula: copy to clipboard
    Please Login or Register  to view this content.


    adapt as necessary
    Richard Buttrey

    If any of the responses have helped then please consider rating them by clicking the small star icon below the post.

  4. #4
    Forum Contributor
    Join Date
    04-09-2013
    Location
    india
    MS-Off Ver
    Excel 2010
    Posts
    156

    Re: Vlookup formula not working

    Dear Richard and Jason
    Good evening
    Thank you very much for your help. I have other special characters like ],/ along with * will this formula will work for all special characters? when i use star as wild character excel considering all to retrieve data and this is making problem for me. you formulas working fine with example data but it is not working good in my real data.
    Thanks in advance
    Regards,

  5. #5
    Forum Expert
    Join Date
    06-08-2012
    Location
    Left the forum!
    MS-Off Ver
    Left the forum!
    Posts
    5,189

    Re: Vlookup formula not working

    As far as I can remember, the only characters that will cause any issues are * ? and ~, this formula should take care of all of them.

    =VLOOKUP(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(A7,"~","~~"),"*","~*"),"?","~?"),$A$2:$B$3,2,FALSE)
    Last edited by jason.b75; 07-09-2018 at 11:37 AM.

  6. #6
    Forum Contributor
    Join Date
    04-09-2013
    Location
    india
    MS-Off Ver
    Excel 2010
    Posts
    156

    Re: Vlookup formula not working

    Thank you very Jason.B75 for your valuable help and suggestion towards my problem

  7. #7
    Forum Expert
    Join Date
    06-08-2012
    Location
    Left the forum!
    MS-Off Ver
    Left the forum!
    Posts
    5,189

    Re: Vlookup formula not working

    You're welcome!
    Thanks for the feedback.

    I just noticed a typo in the formula, I've edited my original reply to correct it.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. VLOOKUP Formula not working
    By matrixpom in forum Excel General
    Replies: 8
    Last Post: 05-18-2017, 05:32 PM
  2. [SOLVED] VLookup formula not working
    By lookupquestion2 in forum Excel Formulas & Functions
    Replies: 12
    Last Post: 05-05-2017, 09:52 AM
  3. VBA Vlookup formula not working
    By jasonmcasey in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 08-06-2014, 06:22 AM
  4. Vlookup value not working because of formula?
    By mikey42979 in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 05-07-2014, 12:11 AM
  5. VLOOKUP formula not working
    By maryren in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 08-08-2013, 05:26 PM
  6. Vlookup Formula not working...
    By Swambo in forum Excel Formulas & Functions
    Replies: 8
    Last Post: 12-15-2011, 05:31 PM
  7. Vlookup formula not working
    By rbwen in forum Excel General
    Replies: 7
    Last Post: 12-08-2011, 08:12 PM

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1