Lookup partial match from a list

G

godanny

New Member
Joined
May 6, 2009
Messages
2
Hi All,

I have a workbook containing two worksheets. On Sheet1 there is a list of strings (about 1000 lines) that are updated monthly. For some of the strings only one word remains the same from month to month.

On Sheet2, there is a list of common words from the list of strings on Sheet1 and a list of corresponding codes for those words.

I need to match each string on Sheet1 to the list of partial matches on Sheet2 and return the corresponding code for each match.

What is the most efficient way of doing this? I can nest MATCH functions in an IF statement but it seems very long winded and the formula was getting too long. Is there a way to lookup the strings on Sheet1 by referencing the CRITERIA list on Sheet2 as a whole and returning the CLASSIFICATION code to cell B2 on Sheet1.

Any help would be much appreciated!!


Sheet1

<table style="border-collapse: collapse;" border="0" width="376" cellpadding="0" cellspacing="0" height="264"><col style="width: 127pt;" width="169"> <col style="width: 104pt;" width="138"> <tbody><tr><td align="center" valign="top">
</td><td align="center" valign="top">A</td><td align="center" valign="top">B
</td></tr><tr style="height: 15pt;" height="20"> <td valign="top">1
</td><td class="xl64" style="height: 15pt; width: 127pt;" width="169" height="20">REFERENCE</td> <td class="xl64" style="border-left: medium none; width: 104pt;" width="138">CLASSIFICATION</td> </tr> <tr style="height: 15pt;" height="20"> <td valign="top">2
</td><td class="xl63" style="border-top: medium none; height: 15pt;" height="20">Distribution Variance</td> <td class="xl63" style="border-top: medium none; border-left: medium none;">
</td> </tr> <tr style="height: 15pt;" height="20"> <td valign="top">3
</td><td class="xl63" style="border-top: medium none; height: 15pt;" height="20">Intraco transfers</td> <td class="xl63" style="border-top: medium none; border-left: medium none;">
</td> </tr> <tr style="height: 15pt;" height="20"> <td valign="top">4
</td><td class="xl63" style="border-top: medium none; height: 15pt;" height="20">100200 Purchases</td> <td class="xl63" style="border-top: medium none; border-left: medium none;">
</td> </tr> <tr style="height: 15pt;" height="20"> <td valign="top">5
</td><td class="xl63" style="border-top: medium none; height: 15pt;" height="20">100201 Purchases</td> <td class="xl63" style="border-top: medium none; border-left: medium none;">
</td> </tr> <tr style="height: 15pt;" height="20"> <td valign="top">6
</td><td class="xl63" style="border-top: medium none; height: 15pt;" height="20">79000001 Receivables</td> <td class="xl63" style="border-top: medium none; border-left: medium none;">
</td> </tr> <tr style="height: 15pt;" height="20"> <td valign="top">7
</td><td class="xl63" style="border-top: medium none; height: 15pt;" height="20">Freight Charge</td> <td class="xl63" style="border-top: medium none; border-left: medium none;">
</td> </tr> <tr style="height: 15pt;" height="20"> <td valign="top">8
</td><td class="xl63" style="border-top: medium none; height: 15pt;" height="20">Other
</td> <td class="xl63" style="border-top: medium none; border-left: medium none;">
</td> </tr> <tr style="height: 15pt;" height="20"> <td valign="top">9
</td><td class="xl63" style="border-top: medium none; height: 15pt;" height="20">Other</td> <td class="xl63" style="border-top: medium none; border-left: medium none;">
</td> </tr> <tr style="height: 15pt;" height="20"> <td valign="top">
</td><td class="xl63" style="border-top: medium none; height: 15pt;" height="20">
</td> <td class="xl63" style="border-top: medium none; border-left: medium none;">
</td> </tr> </tbody></table>
Sheet2
<table style="border-collapse: collapse; width: 256pt;" border="0" width="342" cellpadding="0" cellspacing="0"><col style="width: 128pt;" span="2" width="171"> <tbody><tr><td valign="top">
</td><td align="center" valign="top">A

</td><td align="center" valign="top">B


</td></tr><tr style="height: 15pt;" height="20"> <td valign="top">1
</td><td class="xl66" style="height: 15pt; width: 128pt;" width="171" height="20">CRITERIA</td> <td class="xl66" style="border-left: medium none; width: 128pt;" width="171">CLASSIFICATION</td> </tr> <tr style="height: 15pt;" height="20"> <td valign="top">2
</td><td class="xl65" style="border-top: medium none; height: 15pt;" height="20">Distribution</td> <td class="xl65" style="border-top: medium none; border-left: medium none;">IP</td> </tr> <tr style="height: 15pt;" height="20"> <td valign="top">3
</td><td class="xl65" style="border-top: medium none; height: 15pt;" height="20">Intraco</td> <td class="xl65" style="border-top: medium none; border-left: medium none;">INTRACO</td> </tr> <tr style="height: 15pt;" height="20"> <td valign="top">4
</td><td class="xl65" style="border-top: medium none; height: 15pt;" height="20">Purchases</td> <td class="xl65" style="border-top: medium none; border-left: medium none;">IP</td> </tr> <tr style="height: 15pt;" height="20"> <td valign="top">5
</td><td class="xl65" style="border-top: medium none; height: 15pt;" height="20">Purchases</td> <td class="xl65" style="border-top: medium none; border-left: medium none;">IP</td> </tr> <tr style="height: 15pt;" height="20"> <td valign="top">6
</td><td class="xl65" style="border-top: medium none; height: 15pt;" height="20">Receivables</td> <td class="xl65" style="border-top: medium none; border-left: medium none;">IP</td> </tr> <tr style="height: 15pt;" height="20"> <td valign="top">7
</td><td class="xl65" style="border-top: medium none; height: 15pt;" height="20">Freight</td> <td class="xl65" style="border-top: medium none; border-left: medium none;">FREIGHT</td> </tr> <tr style="height: 15pt;" height="20"> <td valign="top">
</td><td class="xl65" style="border-top: medium none; height: 15pt;" height="20">
</td> <td class="xl65" style="border-top: medium none; border-left: medium none;">
</td> </tr> </tbody></table>
 
Sort by date Sort by votes
Try:

<title>Excel Jeanie HTML</title>Sheet2

<table style="background-color: rgb(255, 255, 255); padding-left: 2pt; padding-right: 2pt; font-family: Arial,Arial; font-size: 10pt;" border="1" cellpadding="0" cellspacing="0"> <colgroup> <col style="width: 30px; font-weight: bold;"> <col style="width: 116px;"> <col style="width: 136px;"></colgroup> <tbody> <tr style="text-align: center; background-color: rgb(202, 202, 202); font-size: 8pt; font-weight: bold;"> <td> </td> <td>A</td> <td>B</td></tr> <tr style="height: 34px;"> <td style="text-align: center; background-color: rgb(202, 202, 202); font-size: 8pt;">1</td> <td style="font-family: Verdana; font-weight: bold;">CRITERIA</td> <td style="font-family: Verdana; font-weight: bold;">CLASSIFICATION</td></tr> <tr style="height: 17px;"> <td style="text-align: center; background-color: rgb(202, 202, 202); font-size: 8pt;">2</td> <td style="font-family: Verdana;">Distribution</td> <td style="font-family: Verdana;">IP</td></tr> <tr style="height: 17px;"> <td style="text-align: center; background-color: rgb(202, 202, 202); font-size: 8pt;">3</td> <td style="font-family: Verdana;">Intraco</td> <td style="font-family: Verdana;">INTRACO</td></tr> <tr style="height: 17px;"> <td style="text-align: center; background-color: rgb(202, 202, 202); font-size: 8pt;">4</td> <td style="font-family: Verdana;">Purchases</td> <td style="font-family: Verdana;">IP</td></tr> <tr style="height: 17px;"> <td style="text-align: center; background-color: rgb(202, 202, 202); font-size: 8pt;">5</td> <td style="font-family: Verdana;">Receivables</td> <td style="font-family: Verdana;">IP</td></tr> <tr style="height: 17px;"> <td style="text-align: center; background-color: rgb(202, 202, 202); font-size: 8pt;">6</td> <td style="font-family: Verdana;">Freight</td> <td style="font-family: Verdana;">FREIGHT</td></tr></tbody></table>

<title>Excel Jeanie HTML</title>Sheet1

<table style="background-color: rgb(255, 255, 255); padding-left: 2pt; padding-right: 2pt; font-family: Arial,Arial; font-size: 10pt;" border="1" cellpadding="0" cellspacing="0"> <colgroup> <col style="width: 30px; font-weight: bold;"> <col style="width: 181px;"> <col style="width: 149px;"></colgroup> <tbody> <tr style="text-align: center; background-color: rgb(202, 202, 202); font-size: 8pt; font-weight: bold;"> <td> </td> <td>A</td> <td>B</td></tr> <tr style="height: 17px;"> <td style="text-align: center; background-color: rgb(202, 202, 202); font-size: 8pt;">1</td> <td style="font-family: Verdana; font-weight: bold;">REFERENCE</td> <td style="font-family: Verdana; font-weight: bold;">CLASSIFICATION</td></tr> <tr style="height: 17px;"> <td style="text-align: center; background-color: rgb(202, 202, 202); font-size: 8pt;">2</td> <td style="font-family: Verdana;">Distribution Variance</td> <td>IP</td></tr> <tr style="height: 17px;"> <td style="text-align: center; background-color: rgb(202, 202, 202); font-size: 8pt;">3</td> <td style="font-family: Verdana;">Intraco transfers</td> <td>INTRACO</td></tr> <tr style="height: 17px;"> <td style="text-align: center; background-color: rgb(202, 202, 202); font-size: 8pt;">4</td> <td style="font-family: Verdana;">100200 Purchases</td> <td>IP</td></tr> <tr style="height: 17px;"> <td style="text-align: center; background-color: rgb(202, 202, 202); font-size: 8pt;">5</td> <td style="font-family: Verdana;">100201 Purchases</td> <td>IP</td></tr> <tr style="height: 17px;"> <td style="text-align: center; background-color: rgb(202, 202, 202); font-size: 8pt;">6</td> <td style="font-family: Verdana;">79000001 Receivables</td> <td>IP</td></tr> <tr style="height: 17px;"> <td style="text-align: center; background-color: rgb(202, 202, 202); font-size: 8pt;">7</td> <td style="font-family: Verdana;">Freight Charge</td> <td>FREIGHT</td></tr> <tr style="height: 17px;"> <td style="text-align: center; background-color: rgb(202, 202, 202); font-size: 8pt;">8</td> <td style="font-family: Verdana;">Other</td> <td> </td></tr> <tr style="height: 17px;"> <td style="text-align: center; background-color: rgb(202, 202, 202); font-size: 8pt;">9</td> <td style="font-family: Verdana;">Other</td> <td> </td></tr></tbody></table>
<table style="border-style: groove; border-color: rgb(0, 255, 0); background-color: rgb(255, 252, 249); font-family: Arial; color: rgb(0, 0, 0); font-size: 10pt;"> <tbody> <tr> <td>Spreadsheet Formulas</td></tr> <tr> <td> <table style="font-family: Arial; font-size: 9pt;" border="1" cellpadding="2" cellspacing="0"> <tbody> <tr style="background-color: rgb(202, 202, 202); font-size: 10pt;"> <td>Cell</td> <td>Formula</td></tr> <tr> <td>B2</td> <td>=IF(ISNA(LOOKUP(10^10,FIND(Sheet2!$A$2:$A$6,A2),Sheet2!$B$2:$B$6)),"",LOOKUP(10^10,FIND(Sheet2!$A$2:$A$6,A2),Sheet2!$B$2:$B$6))</td></tr></tbody></table></td></tr></tbody></table>
Copy B2 down

HTH
 
Upvote 0
Excel Workbook
ABC
1Sheet1**
2***
3*AB
41REFERENCECLASSIFICATION
52Distribution VarianceIP
63Intraco transfersINTRACO
74100200 PurchasesIP
85100201 PurchasesIP
9679000001 ReceivablesIP
107Freight ChargeFREIGHT
118Other#N/A
129Other#N/A
13***
14***
15Sheet2**
16*AB
17***
18***
191CRITERIACLASSIFICATION
202DistributionIP
213IntracoINTRACO
224PurchasesIP
235PurchasesIP
246ReceivablesIP
257FreightFREIGHT
Sheet



Hardeep kanwar
 
Upvote 0
godanny said:
Hi All,

I have a workbook containing two worksheets. On Sheet1 there is a list of strings (about 1000 lines) that are updated monthly. For some of the strings only one word remains the same from month to month.

On Sheet2, there is a list of common words from the list of strings on Sheet1 and a list of corresponding codes for those words.

I need to match each string on Sheet1 to the list of partial matches on Sheet2 and return the corresponding code for each match.

What is the most efficient way of doing this? I can nest MATCH functions in an IF statement but it seems very long winded and the formula was getting too long. Is there a way to lookup the strings on Sheet1 by referencing the CRITERIA list on Sheet2 as a whole and returning the CLASSIFICATION code to cell B2 on Sheet1.

Any help would be much appreciated!!


Sheet1

<TABLE style="BORDER-COLLAPSE: collapse" border=0 cellSpacing=0 cellPadding=0 width=376 height=264><COLGROUP><COL style="WIDTH: 127pt" width=169><COL style="WIDTH: 104pt" width=138><TBODY><TR><TD vAlign=top align=middle>

</TD><TD vAlign=top align=middle>A</TD><TD vAlign=top align=middle>B

</TD></TR><TR style="HEIGHT: 15pt" height=20><TD vAlign=top>1

</TD><TD style="WIDTH: 127pt; HEIGHT: 15pt" class=xl64 height=20 width=169>REFERENCE</TD><TD style="BORDER-LEFT: medium none; WIDTH: 104pt" class=xl64 width=138>CLASSIFICATION</TD></TR><TR style="HEIGHT: 15pt" height=20><TD vAlign=top>2

</TD><TD style="HEIGHT: 15pt; BORDER-TOP: medium none" class=xl63 height=20>Distribution Variance</TD><TD style="BORDER-LEFT: medium none; BORDER-TOP: medium none" class=xl63>

</TD></TR><TR style="HEIGHT: 15pt" height=20><TD vAlign=top>3

</TD><TD style="HEIGHT: 15pt; BORDER-TOP: medium none" class=xl63 height=20>Intraco transfers</TD><TD style="BORDER-LEFT: medium none; BORDER-TOP: medium none" class=xl63>

</TD></TR><TR style="HEIGHT: 15pt" height=20><TD vAlign=top>4

</TD><TD style="HEIGHT: 15pt; BORDER-TOP: medium none" class=xl63 height=20>100200 Purchases</TD><TD style="BORDER-LEFT: medium none; BORDER-TOP: medium none" class=xl63>

</TD></TR><TR style="HEIGHT: 15pt" height=20><TD vAlign=top>5

</TD><TD style="HEIGHT: 15pt; BORDER-TOP: medium none" class=xl63 height=20>100201 Purchases</TD><TD style="BORDER-LEFT: medium none; BORDER-TOP: medium none" class=xl63>

</TD></TR><TR style="HEIGHT: 15pt" height=20><TD vAlign=top>6

</TD><TD style="HEIGHT: 15pt; BORDER-TOP: medium none" class=xl63 height=20>79000001 Receivables</TD><TD style="BORDER-LEFT: medium none; BORDER-TOP: medium none" class=xl63>

</TD></TR><TR style="HEIGHT: 15pt" height=20><TD vAlign=top>7

</TD><TD style="HEIGHT: 15pt; BORDER-TOP: medium none" class=xl63 height=20>Freight Charge</TD><TD style="BORDER-LEFT: medium none; BORDER-TOP: medium none" class=xl63>

</TD></TR><TR style="HEIGHT: 15pt" height=20><TD vAlign=top>8

</TD><TD style="HEIGHT: 15pt; BORDER-TOP: medium none" class=xl63 height=20>Other

</TD><TD style="BORDER-LEFT: medium none; BORDER-TOP: medium none" class=xl63>

</TD></TR><TR style="HEIGHT: 15pt" height=20><TD vAlign=top>9

</TD><TD style="HEIGHT: 15pt; BORDER-TOP: medium none" class=xl63 height=20>Other</TD><TD style="BORDER-LEFT: medium none; BORDER-TOP: medium none" class=xl63>

</TD></TR><TR style="HEIGHT: 15pt" height=20><TD vAlign=top>

</TD><TD style="HEIGHT: 15pt; BORDER-TOP: medium none" class=xl63 height=20>

</TD><TD style="BORDER-LEFT: medium none; BORDER-TOP: medium none" class=xl63>

</TD></TR></TBODY></TABLE>
Sheet2
<TABLE style="WIDTH: 256pt; BORDER-COLLAPSE: collapse" border=0 cellSpacing=0 cellPadding=0 width=342><COLGROUP><COL style="WIDTH: 128pt" span=2 width=171><TBODY><TR><TD vAlign=top>

</TD><TD vAlign=top align=middle>A


</TD><TD vAlign=top align=middle>B



</TD></TR><TR style="HEIGHT: 15pt" height=20><TD vAlign=top>1

</TD><TD style="WIDTH: 128pt; HEIGHT: 15pt" class=xl66 height=20 width=171>CRITERIA</TD><TD style="BORDER-LEFT: medium none; WIDTH: 128pt" class=xl66 width=171>CLASSIFICATION</TD></TR><TR style="HEIGHT: 15pt" height=20><TD vAlign=top>2

</TD><TD style="HEIGHT: 15pt; BORDER-TOP: medium none" class=xl65 height=20>Distribution</TD><TD style="BORDER-LEFT: medium none; BORDER-TOP: medium none" class=xl65>IP</TD></TR><TR style="HEIGHT: 15pt" height=20><TD vAlign=top>3

</TD><TD style="HEIGHT: 15pt; BORDER-TOP: medium none" class=xl65 height=20>Intraco</TD><TD style="BORDER-LEFT: medium none; BORDER-TOP: medium none" class=xl65>INTRACO</TD></TR><TR style="HEIGHT: 15pt" height=20><TD vAlign=top>4

</TD><TD style="HEIGHT: 15pt; BORDER-TOP: medium none" class=xl65 height=20>Purchases</TD><TD style="BORDER-LEFT: medium none; BORDER-TOP: medium none" class=xl65>IP</TD></TR><TR style="HEIGHT: 15pt" height=20><TD vAlign=top>5

</TD><TD style="HEIGHT: 15pt; BORDER-TOP: medium none" class=xl65 height=20>Purchases</TD><TD style="BORDER-LEFT: medium none; BORDER-TOP: medium none" class=xl65>IP</TD></TR><TR style="HEIGHT: 15pt" height=20><TD vAlign=top>6

</TD><TD style="HEIGHT: 15pt; BORDER-TOP: medium none" class=xl65 height=20>Receivables</TD><TD style="BORDER-LEFT: medium none; BORDER-TOP: medium none" class=xl65>IP</TD></TR><TR style="HEIGHT: 15pt" height=20><TD vAlign=top>7

</TD><TD style="HEIGHT: 15pt; BORDER-TOP: medium none" class=xl65 height=20>Freight</TD><TD style="BORDER-LEFT: medium none; BORDER-TOP: medium none" class=xl65>FREIGHT</TD></TR><TR style="HEIGHT: 15pt" height=20><TD vAlign=top>

</TD><TD style="HEIGHT: 15pt; BORDER-TOP: medium none" class=xl65 height=20>

</TD><TD style="BORDER-LEFT: medium none; BORDER-TOP: medium none" class=xl65>

</TD></TR></TBODY></TABLE>
Click to expand...

Select A2:A7 on Sheet2, name the selected range CRITERIA via the Name Box on the Formula Bar. Name B2:B7 on the same sheet CLASSIFICATIONS.

Define also BigNum via Insert|Name|Define as referring to:

9.99999999999999E+307

as given Excel's Help file and not as one of the zillions of variations.

On Sheet1, invoke the following formula:

B2, copy down:

=LOOKUP(BigNum,SEARCH(CRITERIA,A2),CLASSIFICATIONS)

Since you have lots of cells to check, it's advisable to accept #N/A's that might occur instead of suppressing them. Otherwise, you need something like:

Excel 2007...

=IFERROR(LOOKUP(BigNum,SEARCH(CRITERIA,A2),CLASSIFICATIONS),"")

Prior 2007...

Define BigText as referring to:

=REPT("z",255)

and invoke:

=LOOKUP(BigText,CHOOSE({1,2},"",LOOKUP(BigNum,SEARCH(CRITERIA,A2),CLASSIFICATIONS)))
 
Upvote 0
I have a similar problem.
I have a master list of 50+ employees.
I run two reports for payroll which include data for all the employees that worked during that period.
Most of the employees are on both reports but 5-10 are not on one and 5-10 different employees are not on the other.

I paste the data from the two reports onto sheet1 and sheet2 of an Excel file.

I use sheet3 to calculate the totals from both reports.

Problem:
I need each employee to be on the same row on all three sheets so that my formulas match up.

I need "Amy" to be on row 2 and "Zach" to be on row 50 regardless of how many people in between are not on one or both lists.

Can this be done?

Right now I have to go down both reports and insert blank rows where one employee is missing from that report. It is the most painful time consuming hour of my day.

Please help me!
 
Upvote 0
MikeMaurin said:
I have a similar problem.
I have a master list of 50+ employees.
I run two reports for payroll which include data for all the employees that worked during that period.
Most of the employees are on both reports but 5-10 are not on one and 5-10 different employees are not on the other.

I paste the data from the two reports onto sheet1 and sheet2 of an Excel file.

I use sheet3 to calculate the totals from both reports.

Problem:
I need each employee to be on the same row on all three sheets so that my formulas match up.

I need "Amy" to be on row 2 and "Zach" to be on row 50 regardless of how many people in between are not on one or both lists.

Can this be done?

Right now I have to go down both reports and insert blank rows where one employee is missing from that report. It is the most painful time consuming hour of my day.

Please help me!
Click to expand...

Mike, for such a small number of employees sounds like a VLOOKUP is all you need. Can you post more details.
 
Upvote 0
Masterlist-ReportA-ReportB
Amy------Amy-----Brooke
Brooke----Charlie--Charlie
Charlie----David---David
David--------------Emily
Emily-------------------

I need to find a way to make all of these lists exactly 5 rows long so that my formulas all work even when some data is missing.

I need it to look like this:

Masterlist ReportA ReportB
Amy------Amy-----------
Brooke------------Brooke
Charlie---Charlie---Charlie
David----David----David
Emily--------------Emily

So that the formula =(B2+(B3*3)+(B4*4)) will work when pulled down the length of the spreadsheet.

_____

I am not sure how to use a Vlookup to accomplish this. I have used vlookup before but I don't see how to apply it here.

Thanks in advance.
 
Last edited:
Upvote 0
Aladin, can you please refer me to where to go to understand what you are doing with lookup(), BigNum and BigText? Or explain it? :)

Thanks!!
Tai
 
Upvote 0
You must log in or register to reply here.

Similar threads

D
  • Solved
VBA Function to Paste Table into Outlook Email Resulting in HTML language in Email
Replies
8
Views
886
smozgur
smozgur
I
  • Question Question
Sum column by priority (Y-axis) and date (Y-axis)
Replies
3
Views
287
kvsrinivasamurthy
K
P
  • Question Question
Select the time from the list and search with Selenium VBA
Replies
6
Views
822
John_w
J
E
  • Question Question
VBA for Web Scraping help
Replies
0
Views
1K
epactheactor
E
E
  • Question Question
VBA code clean up
Replies
9
Views
695
mumps
mumps

Forum statistics

Threads
1,226,850
Messages
6,193,355
Members
453,790
Latest member
yassinosnoo1

Share this page

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
Back
Top