# Need help in mapping pincode

1. ## Need help in mapping pincode

Dear All,

I have been trying to use vlookup and partial match the pin codes for routing issue. I have attached the excel sheet with the data and the formula am using "= Vlookup(left(A2,4)&"*",b2:b1335,1,0)". Am only able to map a few but for rest am getting #N/A. Need help in solving the problem.

2. ## Re: Need help in mapping pincode

Where is your formula? Where should the results come?
Your use of VLOOKUP is incorrect or at least doubtful

3. ## Re: Need help in mapping pincode

Vlookup formula is right. But I haven't inserted it in the sample sheet attached. I want the data in the third column (C).

4. ## Re: Need help in mapping pincode

The VLOOKUP formula is NOT right, otherwise it would work, and as already mentioned, the syntax is wrong.

Fill in 10-20 rows of expected results MANUALLY and then post the workbook again.

5. ## Re: Need help in mapping pincode

Hi Ali,

My mistake, in the formula I had to convert number to text, now I have changed the data from numeric to text but I still don't get the desired result can you please help. attached the corrected sheet where results for the formula have been inserted.

6. ## Re: Need help in mapping pincode

It is still not clear what you are trying to do
Your "unorthodox " use of VLOOKUP will return the first value in col D starting with the 4 first numbers of the cell in col C
BUT in col D there is more than one value that satisfies this condition ( e.g. for E2 ther are 10 values starting with the string 6033)?
So, explain in words what you want to achieve

7. ## Re: Need help in mapping pincode

thanks Pepe.. as you have explained after searching for the 4 characters in the string, I would want excel to populate the nearest match for C from D in E column. example 600053 is present in both C and D columns yet excel mapped 600053 in column C to 600004 in column and provided the result which is incorrect. Another example u have 620018 is present in both C and D yet excel hasn't mapped anything against 620018 in column E.

8. ## Re: Need help in mapping pincode

Try anchoring the range:

=VLOOKUP(LEFT(C2,4)&"*",\$D\$2:\$D\$1335,1,0)

9. ## Re: Need help in mapping pincode

Hi ali,

This isn't working as well as the values aren't yet right.

10. ## Re: Need help in mapping pincode

Isn't it? So are you going to give us any clue at all about what is wrong with it?

Apply my version to your file, and then highlight some incorrect answers and type manually to their right what you are expecting, then post the workbook again.

NOBODY likes guessing games.

11. ## Re: Need help in mapping pincode

Here you go I have attached the file highlighting the desired result.

12. ## Re: Need help in mapping pincode

You haven't applied the formula I gave you. I'm not going to waste time going round in circles - sorry. Good luck!

13. ## Re: Need help in mapping pincode

Thanks for the response. However in the new sheet I have applied your formula and still there isn't any go.

14. ## Re: Need help in mapping pincode

You have been asked to explain IN WORDS what you are trying to achieve - you still haven't done this. We do not have infinite time (or, in my case, the patience) to prise the necessary information out of you. If you think I'm going to spend my free time trying to work out (guess) what you mean by 'partial match' when you can't even be bothered to explain it yourself, then you've got another think coming. Sorry, I won't be helping any further.

15. ## Re: Need help in mapping pincode

Ok, it is better to start from beginning
In newest sample, is the yelow area expected results?
If yes, why
Pincode
603309
603109
600053
603310
600045
then
Mapping Pincode
600001
600002
600004
600003
600005
and
Result:
603309
603109
600053
603309
600045

How to determind the results?

16. ## Re: Need help in mapping pincode

Hi Bebo,

Yes yellow highlighted is the expected result. Pincode is the desired pincode to be shipped and mapping pincode is the pincode where my logistics partner is able to ship. So, am mapping my logistics partners pin codes to my pin codes.

So, now am using vlookup so as to map it accordingly.

17. ## Re: Need help in mapping pincode

Still no attempt to explain the logic ...

18. ## Re: Need help in mapping pincode

Ali,

Am trying to use VLOOKUP partial match logic as some pin codes mentioned under pin code heading cannot entirely be mapped to mapping pin code. If it were a straight match there is no need to use any logic just a plain matching. But while doing a partial match, the problem am facing is pin codes that matched completely are not getting mapped properly and its missing the logic of mapping to the nearest match. This is the reason am asking help and the desired result that I want has been highlighted in yellow. This mapping is being done to get routing map to deliver our products through our logistics partner.

19. ## Re: Need help in mapping pincode

All of that we know. What is the LOGIC? That's what you haven't explained. We don't know the logic that you are applying and we are not mind readers. There is little point repeating the same thing - emboldening the text doesn't make it any more informative!!! And it still doesn't tell us what we need to know.

So, for the last time: what is the logic that is needed to find a partial match???

its missing the logic of mapping to the nearest match
That's because you have not yet explained what the logic is. Get it?

'Nearest match' could be interpreted many ways - what is YOUR interpretation of it?

20. ## Re: Need help in mapping pincode

``Please Login or Register  to view this content.``
Maybe somthing like this

"# "& this part is to identify if it is an exact match or not.

21. ## Re: Need help in mapping pincode

popipo

The formula worked partially, while where it isn't a match it has taken the first result it has caught while sorting. It needs to capture the nearest pin code. so we may require further correction in this formula as well. Attached the resultant sheet

22. ## Re: Need help in mapping pincode

It needs to capture the nearest pin code
You will need this:

=IFNA(VLOOKUP(C2,\$D\$2:\$D\$1335,1,0),formula_to_find_nearest_match)

BUT you STILL haven't told us what you mean by 'nearest match'.

Explain how that works. Nearest match above or below? What happens if you have one that is one above and one that is one below - which takes priority?

This is the LOGIC that I have been trying to get you to explain for THREE HOURS now.

23. ## Re: Need help in mapping pincode

Sorry that I couldn't get that point. Nearest match would be match below that takes priority as it would belong to the same region. While nearest match above will move to the next region most of the times so, we need to consider the value that is below.

24. ## Re: Need help in mapping pincode

At last! So, to clarify: ALWAYS an exact match or the nearest below? Yes?

The problem is that a VLOOKUP inexact match looks at the next one UP, which is why it's not working for you.

Please confirm and we'll take it from here.

25. ## Re: Need help in mapping pincode

Yes that's right. And when I say nearest its not the first option that it gets needs to be displayed but the right nearest one. Example for 603310 the nearest would be 603309 not 603301. that needs to be displayed.

26. ## Re: Need help in mapping pincode

Try this:

=IFNA(VLOOKUP(--C2,--\$D\$2:\$D\$1335,1,0),VLOOKUP(--LEFT(C2,5)*10,--\$D\$2:\$D\$1335,1,1))

27. ## Re: Need help in mapping pincode

It is working but still as informed above for 603313 its still showing 603309 but not 603312 that is present

28. ## Re: Need help in mapping pincode

Try this:

=IFNA(VLOOKUP(--C2,--\$D\$2:\$D\$1335,1,0),VLOOKUP(--C2,--\$D\$2:\$D\$1335,1,1))

29. ## Re: Need help in mapping pincode

yes it has worked. Thanks a lot. Sorry for not providing the exact information upfront.

30. ## Re: Need help in mapping pincode

We would very much like to help you with your query, however it has been brought to our attention that the same query has been posted on one or more other forums and you have not provided the required cross-post link(s) here.

Please see Forum Rule #3 about cross-posting and adjust accordingly. Read this to understand why we (and other sites like us) consider this to be important.

(Note: this requirement is not optional. As you are new, I'll provide it for you today: https://www.mrexcel.com/board/thread...ncode.1167116/.)

Also, you may not be aware that you can thank those who have helped you by clicking the small star icon located in the lower left corner of the post in which the help was given. By doing so you can add to the reputation(s) of ALL those who helped.

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