Lookup partial match from a list

[godanny]

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>

 

[avsr]

hi friends,
I hve a weekly calendar where fr each date i hve to enter some data...once if week changes that data shud b refreshed and so that i enter new data there...but old datas shud b preserved.

MY idea:

for this ..i have to save the data in other sheet which acts like a database based on year,month,date and time as parameters

but i need to hve this database in column wise like

content year month day time
------------------------------------

so i dont know in which cell the content is to be added...it can b anywhere in the range(name of the range is 'mon').
so how to find where data is added in that range

and then save that data in a cell on other sheet....

plz do help me..its urgent

 

[Aladin Akyurek]

hi friends,
I hve a weekly calendar where fr each date i hve to enter some data...once if week changes that data shud b refreshed and so that i enter new data there...but old datas shud b preserved.

MY idea:

for this ..i have to save the data in other sheet which acts like a database based on year,month,date and time as parameters

but i need to hve this database in column wise like

content year month day time
------------------------------------

so i dont know in which cell the content is to be added...it can b anywhere in the range(name of the range is 'mon').
so how to find where data is added in that range

and then save that data in a cell on other sheet....

plz do help me..its urgent

Would better if you started a separate thread for this...