Finding string in worksheet

[robthesilent]

Hi all

At work we act as a mail sorting area.
We get mail for many businesses and deal with it as either A) redirection, B) return to sender, or C) delivery to a business that actually exists.

Currently, I have split each company name using text to columns with space as a delimiter. I did this because I thought it would make life easier, but now I'm not sure. Origionally, the information came from a Word document. If it makes no difference, I can trivially put it back to how it was.

What I want is to make an excel sheet with a space for the keyword to be entered, and then it will match where that word appears, lookup how to deal with it and display this information next to the entered keyword.

I started with using cell B1 as the keyword entry space, and this in C1
=INDEX(B23:Q695,MATCH((B1),B23:Q695,1),1)
But it returns nothing but N/A - I'm guessing cause I'm using MATCH on an unsorted list.

I've got two tables - one contains the companies, and the other all the employees of each company, as well as alternative names of each company.

I want to be able to type in a single word, and it will tell me what company that person works at. As a bonus, if it could also tell me what the location details (direct, return to sender or address) of that company, that'd be neat. Also, I'm not sure how to deal with names that occur in more than one company - giving a list of all exact hits would be OK.

Huge thanks!
Rob

This is the first table
<table x:str="" style="border-collapse: collapse; width: 722pt;" width="961" border="0" cellpadding="0" cellspacing="0"><col style="width: 50pt;" width="66"> <col style="width: 136pt;" width="181"> <col style="width: 113pt;" width="150"> <col style="width: 423pt;" width="564"> <tbody><tr style="height: 15pt;" height="20"> <td class="xl24" style="height: 15pt; width: 50pt;" width="66" height="20">Floor</td> <td class="xl24" style="width: 136pt;" width="181">Suite</td> <td class="xl24" style="width: 113pt;" width="150">Ref</td> <td class="xl24" style="width: 423pt;" width="564">Name</td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl24" style="height: 15pt;" height="20">G</td> <td class="xl24" x:num="">1</td> <td class="xl24">BMH</td> <td class="xl24">Beaumonde Homes</td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl24" style="height: 15pt;" height="20">G</td> <td class="xl24" x:num="">2</td> <td class="xl24">SAGE</td> <td class="xl24">Sage Financial Group</td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl24" style="height: 15pt;" x:num="" height="20">1</td> <td class="xl24" x:num="">3</td> <td class="xl24">MCAL</td> <td class="xl24">McAullay and Associates</td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl24" style="height: 15pt;" x:num="" height="20">1</td> <td class="xl24" x:num="">6</td> <td class="xl24">SDA</td> <td class="xl24">Steel Detailing Australia</td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl24" style="height: 15pt;" x:num="" height="20">1</td> <td class="xl24" x:num="">7</td> <td class="xl24">STAWA</td> <td class="xl24">Science Teachers Association of Western Australia</td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl24" style="height: 15pt;" x:num="" height="20">1</td> <td class="xl24" x:num="">8</td> <td class="xl24">
</td> <td class="xl24">
</td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl24" style="height: 15pt;" x:num="" height="20">1</td> <td class="xl24" x:num="">9</td> <td class="xl24">CT</td> <td class="xl24">Clifton Tham</td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl24" style="height: 15pt;" x:num="" height="20">1</td> <td class="xl24" x:num="">10</td> <td class="xl24">WKA</td> <td class="xl24">Warren Keeting & Bruce Simcock</td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl24" style="height: 15pt;" x:num="" height="20">3</td> <td class="xl24" x:num="">16</td> <td class="xl24">WWOP</td> <td class="xl24">Worldwide Online Printing</td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl24" style="height: 15pt;" x:num="" height="20">3</td> <td class="xl24" x:num="">17</td> <td class="xl24">BEFP</td> <td class="xl24">Blue Edge</td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl24" style="height: 15pt;" x:num="" height="20">3</td> <td class="xl24" x:num="">18</td> <td class="xl24">IASA</td> <td class="xl24">International Aviation Safety Authority</td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl24" style="height: 15pt;" x:num="" height="20">3</td> <td class="xl24" x:num="">19</td> <td class="xl24">SKFG</td> <td class="xl24">SKFG</td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl24" style="height: 15pt;" x:num="" height="20">3</td> <td class="xl24" x:num="">20</td> <td class="xl24">SKFG</td> <td class="xl24">SKFG</td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl24" style="height: 15pt;" height="20">
</td> <td class="xl24">
</td> <td class="xl24">RTS</td> <td class="xl24">Return to Sender</td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl24" style="height: 15pt;" height="20">
</td> <td class="xl24">
</td> <td class="xl24">RDIR1</td> <td class="xl24">Redirect 1 : 2 Boans Lane, EAST PERTH WA 6004 - David - 0409 150 953</td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl24" style="height: 15pt;" height="20">
</td> <td class="xl24">
</td> <td class="xl24">RDIR2</td> <td class="xl24">Redirect 2 : Level 1, 45 Royal Street, EAST PERTH WA 6004 - 9221 8811</td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl24" style="height: 15pt;" height="20">
</td> <td class="xl24">
</td> <td class="xl24">RDIR3</td> <td class="xl24">Redirect 3 : PO Box 134, MT HAWTHORN, WA 6915 - 9443 6833</td> </tr> </tbody></table>

This is the second table(or at least an example of it, it's kind of big, but it's just more of the same as here) <table x:str="" style="border-collapse: collapse; width: 814pt;" width="1084" border="0" cellpadding="0" cellspacing="0"><col style="width: 50pt;" width="66"> <col style="width: 136pt;" width="181"> <col style="width: 113pt;" width="150"> <col style="width: 423pt;" width="564"> <col style="width: 92pt;" width="123"> <tbody><tr style="height: 15pt;" height="20"> <td class="xl22" style="height: 15pt; width: 50pt;" width="66" height="20">RDIR3</td> <td class="xl22" style="width: 136pt;" width="181">Kindred</td> <td class="xl22" style="width: 113pt;" width="150">Holdings</td> <td class="xl23" style="width: 423pt;" width="564">
</td> <td class="xl22" style="width: 92pt;" width="123">
</td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl22" style="height: 15pt;" height="20">RDIR3</td> <td class="xl22">Hakol</td> <td class="xl22" style="background: silver none repeat scroll 0% 0%; -moz-background-clip: -moz-initial; -moz-background-origin: -moz-initial; -moz-background-inline-policy: -moz-initial;">Pty Ltd</td> <td class="xl23">
</td> <td class="xl22">
</td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl22" style="height: 15pt;" height="20">RDIR2</td> <td class="xl22">Jeff</td> <td class="xl22">Nolan</td> <td class="xl23">
</td> <td class="xl22">
</td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl22" style="height: 15pt;" height="20">RDIR2</td> <td class="xl22">Venton</td> <td class="xl22" style="background: silver none repeat scroll 0% 0%; -moz-background-clip: -moz-initial; -moz-background-origin: -moz-initial; -moz-background-inline-policy: -moz-initial;">Pty Ltd</td> <td class="xl22">
</td> <td class="xl22">
</td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl22" style="height: 15pt;" height="20">RDIR1</td> <td class="xl22">Grandbridge</td> <td class="xl22">Limited</td> <td class="xl22">
</td> <td class="xl22">
</td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl22" style="height: 15pt;" height="20">RDIR1</td> <td class="xl22">E-Shares</td> <td class="xl22" style="background: silver none repeat scroll 0% 0%; -moz-background-clip: -moz-initial; -moz-background-origin: -moz-initial; -moz-background-inline-policy: -moz-initial;">Pty Ltd</td> <td class="xl22">
</td> <td class="xl22">
</td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl22" style="height: 15pt;" height="20">RDIR1</td> <td class="xl22">Biopharmica</td> <td class="xl22">
</td> <td class="xl22">
</td> <td class="xl22">
</td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl22" style="height: 15pt;" height="20">RDIR1</td> <td class="xl22">Diagnostic</td> <td class="xl22">Array</td> <td class="xl22">Systems</td> <td class="xl22" style="background: silver none repeat scroll 0% 0%; -moz-background-clip: -moz-initial; -moz-background-origin: -moz-initial; -moz-background-inline-policy: -moz-initial;">Pty Ltd</td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl22" style="height: 15pt;" height="20">RDIR1</td> <td class="xl22">David</td> <td class="xl22">Breeze</td> <td class="xl24">
</td> <td class="xl22">
</td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl22" style="height: 15pt;" height="20">RDIR1</td> <td class="xl22">Malcolm</td> <td class="xl22">McColl</td> <td class="xl22">
</td> <td class="xl22">
</td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl22" style="height: 15pt;" height="20">RDIR1</td> <td class="xl22">Charles</td> <td class="xl22">Murphy</td> <td class="xl22">
</td> <td class="xl22">
</td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl22" style="height: 15pt;" height="20">RDIR1</td> <td class="xl22">Seng</td> <td class="xl22">Yap</td> <td class="xl22">
</td> <td class="xl22">
</td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl22" style="height: 15pt;" height="20">RTS</td> <td class="xl22">ACER</td> <td class="xl22">COMPUTERS</td> <td class="xl22">
</td> <td class="xl22">
</td> </tr> </tbody></table>