+ Reply to Thread
Results 1 to 8 of 8

V-lookup failed

  1. #1
    Forum Contributor
    Join Date
    12-08-2018
    Location
    pakistan
    MS-Off Ver
    2010
    Posts
    314

    V-lookup failed

    Hi all,
    i have a problem in Vlookup. i am trying but failed. can anybody tell me how to settle these cell values this?
    Data-1 match to Data-2, there are little bit differences in cell value "-","space",.
    consider A after "-" number consider between every thing should be count formula.
    please see the file.
    thanks.
    Attached Files Attached Files

  2. #2
    Forum Contributor
    Join Date
    11-21-2017
    Location
    Chorley
    MS-Off Ver
    2013
    Posts
    220

    Re: V-lookup failed

    It doesn't matter how little the differences are, if Excel can't find what it considers a match then it won't return one...

    In Data 1 A2 you have A9912AM-HS00100
    In Data 2 B2 you have A9912AM-HS-0010000

    You're trying to find the value A2 and anything after A9912AM-HS00100, but this won't match to A9912AM-HS-0010000 because of this part HS-

    To use a wildcard you'd need to change your lookup value to A9912AM-HS, which would then match to A9912AM-HS-0010000 using a lookup value of A9912AM-HS&"*"

    Make sense?

  3. #3
    Valued Forum Contributor Bo_Ry's Avatar
    Join Date
    09-10-2018
    Location
    BKK, Thailand
    MS-Off Ver
    Excel365
    Posts
    2,216

    Re: V-lookup failed

    Please try E2

    =VLOOKUP(LEFT(A2,6)&"*",$B$2:$C$9,2,0)

  4. #4
    Forum Contributor
    Join Date
    12-08-2018
    Location
    pakistan
    MS-Off Ver
    2010
    Posts
    314

    Re: V-lookup failed

    hi Bo_Ry,
    what about this? please see the file again some changes. i also use but result is differ.
    Attached Images Attached Images
    Attached Files Attached Files

  5. #5
    Forum Moderator Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    Northern Ireland
    MS-Off Ver
    Excel 2013 & 2016
    Posts
    20,668

    Re: V-lookup failed

    Maybe this:

    =VLOOKUP(IFERROR(LEFT(LEFT(A2,SEARCH("-",A2)-1),SEARCH("/",LEFT(A2,SEARCH("-",A2)-1))-1),LEFT(A2,SEARCH("-",A2)-1))&"*",B:C,2,FALSE)
    Attached Files Attached Files
    Glenn



  6. #6
    Valued Forum Contributor Bo_Ry's Avatar
    Join Date
    09-10-2018
    Location
    BKK, Thailand
    MS-Off Ver
    Excel365
    Posts
    2,216

    Re: V-lookup failed

    Please try at E2

    =INDEX($C$2:$C$11,-LOOKUP(1,-MATCH(SUBSTITUTE(SUBSTITUTE(LEFT(A2,{8,10,13}),"-","*"),"/","/*")&"*",$B$2:$B$11,)))

  7. #7
    Forum Contributor
    Join Date
    12-08-2018
    Location
    pakistan
    MS-Off Ver
    2010
    Posts
    314

    Re: V-lookup failed

    Hi Glenn,
    why results are same, please see the pic. and last one is not correct.
    Attached Images Attached Images

  8. #8
    Forum Contributor
    Join Date
    12-08-2018
    Location
    pakistan
    MS-Off Ver
    2010
    Posts
    314

    Re: V-lookup failed

    Hi Bo_Ry,
    its fantastic, super.
    thanks a lot.

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

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