• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Vlookup containing string

J

JonnyDijksman

New Member
Hi Guys


This is my first post and i'm sorry if you get asked this same question a million times but I am at work and don't really have time to trawl the whole site :)


I am trying to use vlookup to collect a phone number from a second worksheet in my workbook. My reference cell will have a name with a title prefix - Mr John Smith. The second worksheet has a column with names, some of which being Mr John Smith, some being REF: John Smith and others being John Smith. In my reference column all of the names are prefixed by Mr or Mrs.


How can I use vlookup to return the mobile number attached to the name in worksheet 2 (called - Mobiles) where I need to search for just the first and second part of the name (not worrying about any prefixes or suffixes).


Thanks


Jonathan
 
Jonathan


Firstly, Welcome to the Chandoo.org forums.


Typically it is a matter of doing an Index/Match combination but you will need to strip off the leading Mr/s etc first


Can you post a sample file with maybe dummy names to show us the format

Refer: http://chandoo.org/forums/topic/posting-a-sample-workbook
 
Here is an example of the layout


http://i50.tinypic.com/2gumi6w.jpg


I want to use the name column on the left as my reference to grab the relevant mobile number on the right based on the corresponding string part.


thx
 
Here is the file in case that pic is not sufficient


https://docs.google.com/spreadsheet/ccc?key=0AgOpAwUBu4T_dGRTSXZyclVkWXpyZkw3ZEszMWtSbHc#gid=0
 
Johnathan


In C2 try

Code: 
=IFERROR(INDEX($G$2:$G$4,MATCH("*"&MID(B2,1+FIND(" ",B2),999)&"*",$H$2:$H$4,0)),"not found")


Copy down
 
That returns #Name? in C2


I have seen other examples of vlookup using partial string match, but I start to lose track when the formula has several components :) I thought there was a way of using the asterisk symbol to specify part of the string in the reference cell, but I am not familiar with the correct syntax for that.
 
Firstly can you re-type all the " marks in the formula,


If that doesn't help, Can you allow public access to the file as I had to make it up myself as there wasn't access
 
https://docs.google.com/spreadsheet/ccc?key=0AgOpAwUBu4T_dGRTSXZyclVkWXpyZkw3ZEszMWtSbHc&userstoinvite=ian.huitson@gmail.com#gid=0


That should be public access now.


Could you explain the 'iferror' and 'MID(B2,1+FIND' part of that as I do not understand what they are for.


Thanks
 
A basic vlookup ( =vlookup(b5,g2:h4,1,0) ) works if I add a 'Mr' to the name column 'G' but there must be a simpler way of recognising that information without having to manually add a prefix or suffix each time
 
JonnyDijksman


I downloaded the file

Copied the formula above and pasted it into C2

Copied C2 down to C5


the values shown are:

[pre]
Code: 
ref	name	mobile
1	Mr John Smith	7956133553
2	Mr Joe Blogs	not found
3	Mrs Anne Dune	not found
4	Mr Jeff Stump	7841126683
[/pre]
If you are you using Excel pre-2007 use:

=IF(ISERROR(INDEX($G$2:$G$4,MATCH("*"&MID(B2,1+FIND(" ",B2),999)&"*",$H$2:$H$4,0))),"not found",INDEX($G$2:$G$4,MATCH("*"&MID(B2,1+FIND(" ",B2),999)&"*",$H$2:$H$4,0)))
 
If you are you using Excel pre-2007 use:

Code: 
=IF(ISERROR(INDEX($G$2:$G$4,MATCH("*"&MID(B2,1+FIND(" ",B2),999)&"*",$H$2:$H$4,0))),"not found",INDEX($G$2:$G$4,MATCH("*"&MID(B2,1+FIND(" ",B2),999)&"*",$H$2:$H$4,0)))
 
Have a look at your file here: https://www.dropbox.com/s/f2d0c7tzulldk39/ExcelExample-1_Hui.xls
 
I see that works on the link you sent me, that is great. Can I just finally ask you to explain the last section as I have no idea what it does.


"*"&MID(B2,1+FIND(" ",B2),999)&"*",$H$2:$H$4,0
 
mid(b2,1+find(" ",b2),999)


Mid extracts the Middle part of cell B2, starting at the position of the first space + 1

and going 999 characters (the rest of the cell)
 
Hi Jonny ,


I am sure your question on the MID function will be answered ; however , if you are interested , I can offer an alternative suggestion :


1. Since you can ensure that the names in column B will always be prefixed by Mr / Mrs , and there will be a space between the prefix and the name , isolate each name in column B , by removing the prefix ; use the FIND function , LEN function , and the RIGHT function for this.


2. Within the names in column H , isolate a string of the same length as the length in step 1. Use the RIGHT and LEN functions for this.


3. Match the two names found in steps 1 and 2 and if they are equal , retrieve the associated number. Use the MATCH and INDEX functions for this.


A sample formula is :


=INDEX($G$2:$G$4,MATCH(RIGHT(B2,LEN(B2)-FIND(" ",B2)),RIGHT($H$2:$H$4,LEN(B2)-FIND(" ",B2)),0))


entered as an array formula , using CTRL SHIFT ENTER.


Narayan
 
Here's a LOOKUP based option. If there's a possibility of case not matching then you shall consider using "SEARCH" in place of "FIND"


=LOOKUP(9.99E+307,FIND(TRIM(MID(B2,FIND(" ",B2,1),999)),$H$1:$H$4,1),$G$1:$G$4)
 
Hi, JonnyDijksman!


If you can use two helper columns, try this:


C2, and copy down thru C5: =SI(ESERROR(INDICE(G$2:G$5;COINCIDIR(D2;I$2:I$4;0)));"not found";INDICE(G$2:G$5;COINCIDIR(D2;I$2:I$4;0))) -----> in english: =IF(ISERROR(INDEX(G$2:G$5,MATCH(D2,I$2:I$4,0))),"not found",INDEX(G$2:G$5,MATCH(D2,I$2:I$4,0)))


D2, and copy down thru D5: =SI(O(IZQUIERDA(MINUSC(B2);2)="mr";IZQUIERDA(MINUSC(B2);3)="mrs");DERECHA(B2;LARGO(B2)-ENCONTRAR(" ";B2));B2) -----> in english: =IF(OR(LEFT(LOWER(B2),2)="mr",LEFT(LOWER(B2),3)="mrs"),RIGHT(B2,LEN(B2)-FIND(" ",B2)),B2)


I2, and copy down thru I4: =SI(O(IZQUIERDA(MINUSC(H2);3)="ref");DERECHA(H2;LARGO(H2)-ENCONTRAR(" ";H2));H2) -----> in english: =IF(OR(LEFT(LOWER(H2),3)="ref"),RIGHT(H2,LEN(H2)-FIND(" ",H2)),H2)


Regards!
 
Hey guys


Thanks for all your advice. I have used bits and pieces from all your suggestions and managed to amalgamate a working formula :) It is called 'logic' but it is so damn complicated lol :)


Regards
 
<p>What does your job as an Immigration Officer entail? ,http://theweekendrider.com/activity/p/47123/
I profile all passengers or people wanting to?immigrate to New Zealand black detroit lions jersey ?and identify potential high risk passengers. We have the ability to prevent entry to passengers who don’t meet entry requirements. We use sophisticated IT systems to track passengers prior to them arriving in NZ. We have various contacts all over the world that we use to assist us in communicating with passengers. We also assist airlines to process passengers flying in to NZ..</p>
womens lions jersey

<p>Do you conduct any interviews with potential migrants?


We interact with passengers onshore in NZ. This could be for a variety of reasons which include immigration matters relating to visas and interviews to determine passengers visiting intentions, tracking passengers who have overstayed their welcome and who are returned to their port of origin.</p>
<p>What authentic lions jerseyhttp://http://detroitlionsjerseyss.blogspot.com/2012/07/authentic-lions-jersey.html are your working conditions like? Do you detroit lions black jerseyhttp://http://detroitlionsjerseyss.blogspot.com/2012/07/detroit-lions-black-jersey.html use any equipment?
My working environment is very secure and access is strictly regulated. We have some of the most advanced IT systems available. Our documentation examination equipment is state of the art.</p>
<p>Have you had significant experiences dealing with migrants?
I encounter the usual migrants who arrive with barely enough money to buy a hamburger. As well as the odd mental patient who believed they were persecuted by law enforcement etc.</p>
<p>What makes this job exciting and worthwhile?
The ability to protect New Zealand’s borders through my direct observations and actions makes it worthwhile. New Zealand has no borders,http://www.giist.com/forums/topic.php?id=24222#post-29034, so protecting the country womens lions jerseyhttp://http://detroitlionsjerseyss.blogspot.com/2012/07/womens-lions-jersey.html is a lot easier,http://neohero.org/activity/p/108700/, as the only way in/out is by air or water.</p>
 
In front of e535ed21034ea3nonexistent|hollow|clear|blank2eac3fvalueless4a01d7c4be7e5d9928c2f7cc4b67c39df1441292e are quite easy|uncomplicated,louis vuitton outlet, simple and accidental|fortuitous|haphazard|occasional -- infrequent nice times.Yemen is in fifteenageth Century or early yet had 9424f3052649f322fnonexistent|hollow|clear|blank35b0e8288b51f54ed to grow coffared in the.Tears in 99a6avalueless202bd1e6d01962e80d4a5ce9a4a,abercrombie!Little shopped,michael kors outlet, melancholy|sorrowful, I roamed in the avenue,abercrombie outlet, use your brains,ralph lauren, alsoth fdelay gaging a periodicals flared, becaused wcaps do I f72anonexistent|hollow|clear|blank91ec628d0a7270d65f19ab05f2d3; West are chiefly|effectively|primarily to the climbingain, queried|inquired the fc306c2bfd753ab56aabb8bfac6b0a5oldst differencing is hidinnedg aired power, for why.
Recalled initiunite|league.Like the 6cda6ce30b0b6c54ebbed5f5117cfccd0e of your eyeses.Use a handkerchiefs to wiping away the dust on the windowed."Granddads saided: "Grandpa baby.I put the babies aaccessing|coursing.These yearss notwithstanding|though|however|nevertheless repay|recompense earned fewer, ones not thoughtful|scrupulous|cautious|detailed,cheap air jordans, basketballs so many Hibiscus species, no base you came.
I ambition|absence to sent a pelletedoon,sac longchamp pas cher, bringing yourselves a present.Your shadowing in the centers of the peasants|planterss,longchamp, my old livestock ah,michael kors handbags, if I cares,air jordans, "Hope" in 14 obtained|conquered|triumphedter did not go,sac longchamp, here than Seine River is too|likewise smarting|prettying.The totals widths of 6 kilomeasureds,air jordans shoes, want vigorous|athletic|lively|spirited proposingd to quit|depart you.
Related articles:


Henceforth will I recognize that each day I am tested by life in like manner. If I persist, if I continue to try, if I continue to charge forward, I will succeed.
 
You must log in or register to reply here.
Back
Top