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?
 

Some videos you may like

Excel Facts

What did Pito Salas invent?
Pito Salas, working for Lotus, popularized what would become to be pivot tables. It was released as Lotus Improv in 1989.

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,271
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
 

Subscribe on YouTube

Watch MrExcel Video

Forum statistics

Threads
1,106,228
Messages
5,509,923
Members
408,764
Latest member
Abdul Matin

This Week's Hot Topics

  • Turn fraction around
    Hello I need to turn a fraction around, for example I have 1/3 but I need to present as 3/1
  • TIme Clock record reformatting to ???
    Hello All, I'd like some help formatting this (Tbl-A)(Loaded via Power Query) [ATTACH type="full" width="511px" alt="PQdata.png"]22252[/ATTACH]...
  • TextBox Match
    hi, I am having a few issues with my code below, what I need it to do is when they enter a value in textbox8 (QTY) either 1,2 or 3 the 3 textboxes...
  • Using Large function based on Multiple Criteria
    Hello, I can't seem to get a Large formula to work based on two criteria's. I can easily get a oldest value based one value, but I'm struggling...
  • Can you check my code please
    Hi, Im going round in circles with a Compil Error End With Without With Here is the code [CODE=rich] Private Sub...
  • Combining 2 pivot tables into 1 chart
    Hello everyone, My question sounds simple but I do not know the answer. I have 2 pivot tables and 2 charts that go with this. However I want to...
Top