Partial Name Match on 2 Spreadsheets

[blondisauce]

I have a reference sheet that contains project names & project #'s. I have another sheet where the names do not exactly match but I need to bring in the project #'s. I tried VLOOKUP but didn't work because the names do not exactly match. I also tried INDEX with MATCH but that didn't work either. How do I bring in the project #'s? Is there a way I can attach the 2 spread sheets?

 

[jeffreybrown]

Hello & Welcome to the Board,

No way to attach spreadsheets here, but if you look at my signature block you can use the HTML maker to give us a sample of what you are trying to do and how the two names do not quite exactly match.

 

[VoG]

A fuzzy VLOOKUP may help you http://www.mrexcel.com/board2/viewtopic.php?p=974873#974873

 

[blondisauce]

Hello & Welcome to the Board,

No way to attach spreadsheets here, but if you look at my signature block you can use the HTML maker to give us a sample of what you are trying to do and how the two names do not quite exactly match.

<TABLE style="WIDTH: 396pt; BORDER-COLLAPSE: collapse" cellSpacing=0 cellPadding=0 width=528 border=0><COLGROUP><COL style="WIDTH: 165pt; mso-width-source: userset; mso-width-alt: 8045" width=220><COL style="WIDTH: 173pt; mso-width-source: userset; mso-width-alt: 8448" width=231><COL style="WIDTH: 58pt; mso-width-source: userset; mso-width-alt: 2816" width=77><TBODY><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; WIDTH: 165pt; BORDER-BOTTOM: #ece9d8; HEIGHT: 15pt; BACKGROUND-COLOR: transparent" width=220 height=20>Sheet2</TD><TD style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; WIDTH: 173pt; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent" width=231>Sheet 1</TD><TD style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; WIDTH: 58pt; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent" width=77>Project #'s</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; HEIGHT: 15pt; BACKGROUND-COLOR: transparent" height=20>16TH & CACTUS</TD><TD class=xl63 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent">PHO 16TH CACTUS LTEBH</TD><TD class=xl63 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent">2011639163</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; HEIGHT: 15pt; BACKGROUND-COLOR: transparent" height=20>36TH & LOWER BUCKEYE</TD><TD class=xl63 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent">PHO 35TH AND LOWER BUCKEYE LTE</TD><TD class=xl63 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent">2011633521</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; HEIGHT: 15pt; BACKGROUND-COLOR: transparent" height=20>99TH AVE & BELL</TD><TD class=xl63 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent">PHO 99TH AVE BELL LTEBH</TD><TD class=xl63 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent">2011633511</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; HEIGHT: 15pt; BACKGROUND-COLOR: transparent" height=20>111TH AVE & NORTHERN</TD><TD class=xl63 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent">PHO 111TH AVE NORTHERN LTEBH</TD><TD class=xl63 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent">2011639213</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; HEIGHT: 15pt; BACKGROUND-COLOR: transparent" height=20>BARCELONA (COPPER & WARNER)</TD><TD class=xl63 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent">PHO BARCELONA LTE FTTC</TD><TD class=xl63 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent">2011661637</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; HEIGHT: 15pt; BACKGROUND-COLOR: transparent" height=20>BASELINE & 48TH (ENG)</TD><TD class=xl63 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent">PHO BASELINE 48TH (ENG) LTEBH</TD><TD class=xl63 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent">2011639311</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; HEIGHT: 15pt; BACKGROUND-COLOR: transparent" height=20>BEARDSLEY & 67TH</TD><TD class=xl63 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent">PHO BEARDSLEY 67TH LTEBH</TD><TD class=xl63 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent">2011639185</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; HEIGHT: 15pt; BACKGROUND-COLOR: transparent" height=20>BEARDSLEY & 107TH AVE</TD><TD class=xl63 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent">PHO BEARDSLEY 107TH AVE LTEBH</TD><TD class=xl63 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent">2011622667</TD></TR></TBODY></TABLE>

 

[jeffreybrown]

Sorry just can't think of an appropriate solution here.

 

[Nalani]

Possible solution depending how the Poject Name got into Sheet2 in the first place. Maybe somebody is lazy to type in the whole Name.

Maybe, Define your Project Name range on Sheet1 with say a name like projects

On Sheet2, in my example, A1 use Data Validation >Allow: - LIST > Source: - =projects > OK out

B1 formula =INDEX(Sheet1!A2:B9,MATCH(A1,projects,0),2)

B1 will return a #N/A if A1 is empty, but there are ways around that depending on your version of Excel. Would need to know that.

If this is something you are able to go with, then sombody else can help with the #N/A part. I have to leave for a few hours now.

Sheet2

<TABLE style="BACKGROUND-COLOR: #ffffff; PADDING-LEFT: 2pt; PADDING-RIGHT: 2pt; FONT-FAMILY: Calibri,Arial; FONT-SIZE: 11pt" border=1 cellSpacing=0 cellPadding=0><COLGROUP><COL style="WIDTH: 30px; FONT-WEIGHT: bold"><COL style="WIDTH: 216px"><COL style="WIDTH: 79px"></COLGROUP><TBODY><TR style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt; FONT-WEIGHT: bold"><TD></TD><TD>A</TD><TD>B</TD></TR><TR style="HEIGHT: 22px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">1</TD><TD>PHO 16TH CACTUS LTEBH</TD><TD style="TEXT-ALIGN: right">2011639163</TD></TR><TR style="HEIGHT: 22px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">2</TD><TD></TD><TD>#N/A</TD></TR><TR style="HEIGHT: 22px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">3</TD><TD>PHO BARCELONA LTE FTTC</TD><TD style="TEXT-ALIGN: right">2011639185</TD></TR></TBODY></TABLE>

Excel tables to the web >> http://www.excel-jeanie-html.de/index.php?f=1" target="_blank"> Excel Jeanie HTML 4

I'll check later when I get back.

Good Luck

 

[blondisauce]

I tried over and over - I couldn't get it to work. All it did was input the project #'s from top to bottom

 

[al_b_cnu]

Hi,

Using FuzzyVlookup Algorithm 2 give these results:
<b>Excel 2003</b><table cellpadding="2.5px" rules="all" style=";background-color: #FFFFFF;border: 1px solid;border-collapse: collapse; border-color: #A6AAB6"><colgroup><col width="25px" style="background-color: #E0E0F0" /><col /><col /></colgroup><thead><tr style=" background-color: #E0E0F0;text-align: center;color: #161120"><th></th><th>A</th><th>B</th></tr></thead><tbody><tr ><td style="color: #161120;text-align: center;">1</td><td style="font-weight: bold;;">Lookup</td><td style="font-weight: bold;;">Project #'s</td></tr><tr ><td style="color: #161120;text-align: center;">2</td><td style=";">PHO 16TH CACTUS LTEBH</td><td style="text-align: right;;">2011639163</td></tr><tr ><td style="color: #161120;text-align: center;">3</td><td style=";">PHO 35TH AND LOWER BUCKEYE LTE</td><td style="text-align: right;;">2011633521</td></tr><tr ><td style="color: #161120;text-align: center;">4</td><td style=";">PHO 99TH AVE BELL LTEBH</td><td style="text-align: right;;">2011633511</td></tr><tr ><td style="color: #161120;text-align: center;">5</td><td style=";">PHO 111TH AVE NORTHERN LTEBH</td><td style="text-align: right;;">2011639213</td></tr><tr ><td style="color: #161120;text-align: center;">6</td><td style=";">PHO BARCELONA LTE FTTC</td><td style="text-align: right;;">2011661637</td></tr><tr ><td style="color: #161120;text-align: center;">7</td><td style=";">PHO BASELINE 48TH (ENG) LTEBH</td><td style="text-align: right;;">2011639311</td></tr><tr ><td style="color: #161120;text-align: center;">8</td><td style=";">PHO BEARDSLEY 67TH LTEBH</td><td style="text-align: right;;">2011639185</td></tr><tr ><td style="color: #161120;text-align: center;">9</td><td style=";">PHO BEARDSLEY 107TH AVE LTEBH</td><td style="text-align: right;;">2011622667</td></tr></tbody></table><p style="width:3.6em;font-weight:bold;margin:0;padding:0.2em 0.6em 0.2em 0.5em;border: 1px solid #A6AAB6;border-top:none;text-align: center;background-color: #E0E0F0;color: #161120">Sheet1</p><br /><br />

<b>Excel 2003</b><table cellpadding="2.5px" rules="all" style=";background-color: #FFFFFF;border: 1px solid;border-collapse: collapse; border-color: #A6AAB6"><colgroup><col width="25px" style="background-color: #E0E0F0" /><col /><col /></colgroup><thead><tr style=" background-color: #E0E0F0;text-align: center;color: #161120"><th></th><th>A</th><th>B</th></tr></thead><tbody><tr ><td style="color: #161120;text-align: center;">1</td><td style=";">Lookup</td><td style=";">Result</td></tr><tr ><td style="color: #161120;text-align: center;">2</td><td style=";">16TH & CACTUS</td><td style="text-align: right;;">2011639163</td></tr><tr ><td style="color: #161120;text-align: center;">3</td><td style=";">36TH & LOWER BUCKEYE</td><td style="text-align: right;;">2011633521</td></tr><tr ><td style="color: #161120;text-align: center;">4</td><td style=";">99TH AVE & BELL</td><td style="text-align: right;;">2011633511</td></tr><tr ><td style="color: #161120;text-align: center;">5</td><td style=";">111TH AVE & NORTHERN</td><td style="text-align: right;;">2011639213</td></tr><tr ><td style="color: #161120;text-align: center;">6</td><td style=";">BARCELONA (COPPER & WARNER)</td><td style="text-align: right;;">2011661637</td></tr><tr ><td style="color: #161120;text-align: center;">7</td><td style=";">BASELINE & 48TH (ENG)</td><td style="text-align: right;;">2011639311</td></tr><tr ><td style="color: #161120;text-align: center;">8</td><td style=";">BEARDSLEY & 67TH</td><td style="text-align: right;;">2011639185</td></tr><tr ><td style="color: #161120;text-align: center;">9</td><td style=";">BEARDSLEY & 107TH AVE</td><td style="text-align: right;;">2011622667</td></tr></tbody></table><p style="width:3.6em;font-weight:bold;margin:0;padding:0.2em 0.6em 0.2em 0.5em;border: 1px solid #A6AAB6;border-top:none;text-align: center;background-color: #E0E0F0;color: #161120">Sheet2</p><br /><br /><table width="85%" cellpadding="2.5px" rules="all" style=";border: 2px solid black;border-collapse:collapse;padding: 0.4em;background-color: #FFFFFF" ><tr><td style="padding:6px" ><b>Worksheet Formulas</b><table cellpadding="2.5px" width="100%" rules="all" style="border: 1px solid;text-align:center;background-color: #FFFFFF;border-collapse: collapse; border-color: #A6AAB6"><thead><tr style=" background-color: #E0E0F0;color: #161120"><th width="10px">Cell</th><th style="text-align:left;padding-left:5px;">Formula</th></tr></thead><tbody><tr><th width="10px" style=" background-color: #E0E0F0;color: #161120">B2</th><td style="text-align:left">=fuzzyvlookup(<font color="Blue">A2,Sheet1!A:B,2,,,2</font>)</td></tr><tr><th width="10px" style=" background-color: #E0E0F0;color: #161120">B3</th><td style="text-align:left">=fuzzyvlookup(<font color="Blue">A3,Sheet1!A:B,2,,,2</font>)</td></tr><tr><th width="10px" style=" background-color: #E0E0F0;color: #161120">B4</th><td style="text-align:left">=fuzzyvlookup(<font color="Blue">A4,Sheet1!A:B,2,,,2</font>)</td></tr><tr><th width="10px" style=" background-color: #E0E0F0;color: #161120">B5</th><td style="text-align:left">=fuzzyvlookup(<font color="Blue">A5,Sheet1!A:B,2,,,2</font>)</td></tr><tr><th width="10px" style=" background-color: #E0E0F0;color: #161120">B6</th><td style="text-align:left">=fuzzyvlookup(<font color="Blue">A6,Sheet1!A:B,2,,,2</font>)</td></tr><tr><th width="10px" style=" background-color: #E0E0F0;color: #161120">B7</th><td style="text-align:left">=fuzzyvlookup(<font color="Blue">A7,Sheet1!A:B,2,,,2</font>)</td></tr><tr><th width="10px" style=" background-color: #E0E0F0;color: #161120">B8</th><td style="text-align:left">=fuzzyvlookup(<font color="Blue">A8,Sheet1!A:B,2,,,2</font>)</td></tr><tr><th width="10px" style=" background-color: #E0E0F0;color: #161120">B9</th><td style="text-align:left">=fuzzyvlookup(<font color="Blue">A9,Sheet1!A:B,2,,,2</font>)</td></tr></tbody></table></td></tr></table><br />

The link to the latest FuzzyVlookup code is in my signature, post back if any problems.

 

[blondisauce]

Thanks Alan! I'll try this when I return to work!