+ 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
    2013-365
    Posts
    500

    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
    Valued Forum Contributor PaulSP8's Avatar
    Join Date
    11-21-2017
    Location
    Chorley, England
    MS-Off Ver
    2013 & 2021
    Posts
    437

    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
    Forum Guru Bo_Ry's Avatar
    Join Date
    09-10-2018
    Location
    Thailand
    MS-Off Ver
    MS 365
    Posts
    7,211

    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
    2013-365
    Posts
    500

    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
    Digital Nomad... occasionally based in Ireland.
    MS-Off Ver
    O365 (PC) V 2403
    Posts
    43,893

    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




    None of us get paid for helping you... we do this for fun. So DON'T FORGET to say "Thank You" to all who have freely given some of their time to help YOU.

    Temporary addition of accented to illustrate ongoing problem to the TT: L? fh?ile P?draig sona dhaoibh

  6. #6
    Forum Guru Bo_Ry's Avatar
    Join Date
    09-10-2018
    Location
    Thailand
    MS-Off Ver
    MS 365
    Posts
    7,211

    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
    2013-365
    Posts
    500

    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
    2013-365
    Posts
    500

    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)

Similar Threads

  1. Passed or Failed, I Failed at excel.
    By jschavo in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 04-04-2016, 09:42 PM
  2. [SOLVED] Failed Lookup based on small function
    By MattRNR in forum Excel Formulas & Functions
    Replies: 7
    Last Post: 10-20-2013, 09:03 PM
  3. [SOLVED] Failed Button
    By dgingrey in forum Excel Programming / VBA / Macros
    Replies: 5
    Last Post: 08-28-2013, 09:17 AM
  4. My first chart... failed!
    By RCH in forum Excel Charting & Pivots
    Replies: 2
    Last Post: 09-15-2007, 12:46 AM
  5. Select failed?
    By davegb in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 03-01-2006, 04:25 PM
  6. [SOLVED] Select failed?
    By davegb in forum Excel Programming / VBA / Macros
    Replies: 5
    Last Post: 07-12-2005, 12:05 PM
  7. Paste Failed
    By Nathan Bell in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 01-06-2005, 04:06 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