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.
Thanks in Advance,
Aditya
Where is your formula? Where should the results come?
Your use of VLOOKUP is incorrect or at least doubtful
Vlookup formula is right. But I haven't inserted it in the sample sheet attached. I want the data in the third column (C).
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.
Ali
Enthusiastic self-taught user of MS Excel who's always learning!
Don't forget to say "thank you" in your thread to anyone who has offered you help.
You can reward them by clicking on * Add Reputation below their user name on the left, if you wish.
Forum Rules (updated August 2023): please read them here.
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.
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
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.
Try anchoring the range:
=VLOOKUP(LEFT(C2,4)&"*",$D$2:$D$1335,1,0)
Hi ali,
This isn't working as well as the values aren't yet right.
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.
Here you go I have attached the file highlighting the desired result.
You haven't applied the formula I gave you. I'm not going to waste time going round in circles - sorry. Good luck!
Thanks for the response. However in the new sheet I have applied your formula and still there isn't any go.
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.
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?
Quang PT
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.
Still no attempt to explain the logic ...
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.
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???
That's because you have not yet explained what the logic is. Get it?its missing the logic of mapping to the nearest match
'Nearest match' could be interpreted many ways - what is YOUR interpretation of it?
Last edited by AliGW; 04-05-2021 at 06:38 AM. Reason: Typo
Maybe somthing like this
Please Login or Register to view this content.
"# "& this part is to identify if it is an exact match or not.
Willem
English is not my native language sorry for errors
Please correct me if I'm completely wrong
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
You will need this:It needs to capture the nearest pin code
=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.
Last edited by AliGW; 04-05-2021 at 07:05 AM.
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.
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.
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.
Last edited by swaaditya; 04-05-2021 at 07:12 AM.
Try this:
=IFNA(VLOOKUP(--C2,--$D$2:$D$1335,1,0),VLOOKUP(--LEFT(C2,5)*10,--$D$2:$D$1335,1,1))
It is working but still as informed above for 603313 its still showing 603309 but not 603312 that is present
Try this:
=IFNA(VLOOKUP(--C2,--$D$2:$D$1335,1,0),VLOOKUP(--C2,--$D$2:$D$1335,1,1))
yes it has worked. Thanks a lot. Sorry for not providing the exact information upfront.
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/.)
If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED.
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
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks