Vlookups and match

redrabbit

New Member
Joined
Jan 11, 2008
Messages
32
hello,

I was hoping you can help, i have a spreadsheet that contains just Surnames and I want to do a "lookup" or "match" with another spreadsheet that consists of these surnames and other data.

The other spreadsheet has a column called Full Names which has results like Mr John Smith, or in the form of Smith, John which the vlookup function wont spot.

i have played aboutwith the vlookup/match/index formula but having no such luck.


Anybody got any recommendations on how to do this?
 

Excel Facts

What does custom number format of ;;; mean?
Three semi-colons will hide the value in the cell. Although most people use white font instead.

abi_learn_vba

Board Regular
Joined
Nov 6, 2009
Messages
215
Use Vlookup

Vlookup("Smith", $Your table array$", col_index, 1)

here 1 will pull out similar matches of "Smith"

Regards
Abi
 

AlphaFrog

MrExcel MVP
Joined
Sep 2, 2009
Messages
16,416
Wrap your lookup name in wildcards e.g. "*Smith*"

<table border="1" cellspacing="0" cellpadding="0" style="font-family:Arial,Arial; font-size:10pt; background-color:#ffffff; padding-left:2pt; padding-right:2pt; "> <colgroup><col style="font-weight:bold; width:30px; " /><col style="width:100px;" /><col style="width:17.6px;" /><col style="width:76.8px;" /><col style="width:76px;" /></colgroup><tr style="background-color:#cacaca; text-align:center; font-weight:bold; font-size:8pt; "><td > </td><td >A</td><td >B</td><td >C</td><td >D</td></tr><tr style="height:22px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >1</td><td style="font-weight:bold; ">Full Names</td><td style="font-weight:bold; "> </td><td style="font-weight:bold; ">Last Name</td><td style="font-weight:bold; ">Row Result</td></tr><tr style="height:22px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >2</td><td >Bob Barker</td><td > </td><td >Smith</td><td style="background-color:#ffff99; text-align:center; ">6</td></tr><tr style="height:22px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >3</td><td >Jim Doe</td><td > </td><td >Van</td><td style="background-color:#ffff99; text-align:center; ">7</td></tr><tr style="height:22px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >4</td><td >Jack Jones</td><td > </td><td >Doe</td><td style="background-color:#ffff99; text-align:center; ">3</td></tr><tr style="height:22px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >5</td><td >Al Capp</td><td > </td><td >Bob</td><td style="background-color:#ffff99; text-align:center; ">2</td></tr><tr style="height:22px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >6</td><td >Mr John Smith</td><td > </td><td >Capp</td><td style="background-color:#ffff99; text-align:center; ">5</td></tr><tr style="height:22px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >7</td><td >**** Van Dyke</td><td > </td><td >Jones</td><td style="background-color:#ffff99; text-align:center; ">4</td></tr></table><br /><table style="font-family:Arial; font-size:10pt; border-style: groove ;border-color:#00ff00;background-color:#fffcf9; color:#000000; "><tr><td ><b>Spreadsheet Formulas</b></td></tr><tr><td ><table border = "1" cellspacing="0" cellpadding="2" style="font-family:Arial; font-size:9pt;"><tr style="background-color:#cacaca; font-size:10pt;"><td >Cell</td><td >Formula</td></tr><tr><td >D2</td><td >=MATCH("*"&C2&"*",A:A,0)</td></tr><tr><td >D3</td><td >=MATCH("*"&C3&"*",A:A,0)</td></tr><tr><td >D4</td><td >=MATCH("*"&C4&"*",A:A,0)</td></tr><tr><td >D5</td><td >=MATCH("*"&C5&"*",A:A,0)</td></tr><tr><td >D6</td><td >=MATCH("*"&C6&"*",A:A,0)</td></tr><tr><td >D7</td><td >=MATCH("*"&C7&"*",A:A,0)</td></tr></table></td></tr></table>
 

redrabbit

New Member
Joined
Jan 11, 2008
Messages
32
thank you guys for such a quick reply and helping out a fellow forumite. I really appreciate your help and guidance. :)

I will have a play with your recommendations, I think Alpha Frogs example is going to the trick, thanks abi_learn_vba for the wildcard trick as i didn't know it was possible.

jodders
 

Watch MrExcel Video

Forum statistics

Threads
1,133,325
Messages
5,658,170
Members
418,430
Latest member
Chlwls808

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top