# V-lookup failed

1. ## 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.
thanks.

2. ## 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. ## Re: V-lookup failed

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

hi Bo_Ry,

5. ## 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)

6. ## Re: V-lookup failed

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

7. ## Re: V-lookup failed

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

8. ## Re: V-lookup failed

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

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

#### 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