Ok, I cannot for the life of me figure out how to make this work. Essentially, I would like to write something that would take a complex cell value (a text string in this case), compare that value to another table that has "simplified" listings, and return the "simplified listing" based on a common denominator. So, to illustrate:
Table 1
Column A/ Column B
Row 1: Transaction 123456 Barnes&Nob Site 98765 Los Angelos/ (Formula - Should result in "Barnes & Noble")
Row 2: Transaction 234566 Barnes&Nob Site 98765 New York/ (Formula - Should result in "Barnes & Noble")
Row 3: Transaction 456789 FoodLio Grocery8765 Atlanta/ (Formula - Should result in "Food Lion")
Table 2
Column A/ Column B
Row 1: Barnes&Nob/ Barnes & Noble
Row 2: FoodLio/ Food Lion
Row 3: Sunoco/ Gas
Formula = Compare the cell in table A to table B, identify the line in table B that contains similar text, and then return the value in column 2 of table B. So, in the example above, the formula would take the value in Table A, Row 1 (Transaction 123456 Barnes&Nob Site 98765 Los Angelos), find the value in Table B, Column 1 that has a value contained in the referenced cell (Barnes&Nob) and returns the corresponding cell value from Table B, Column 2 as the final response (Barnes & Noble).
So I started with this as a base but don't know where to go with it - =IF(ISNUMBER(SEARCH("dog",A1)),"dog",""). The issue is that as you can imagine, there are tons of values in column 1 of Table A and in Table B, so no way to be as specific as saying "dog", you have to do some sort of vlookup for a partial, wildcard type match on table B column 1.
Any ideas?
Table 1
Column A/ Column B
Row 1: Transaction 123456 Barnes&Nob Site 98765 Los Angelos/ (Formula - Should result in "Barnes & Noble")
Row 2: Transaction 234566 Barnes&Nob Site 98765 New York/ (Formula - Should result in "Barnes & Noble")
Row 3: Transaction 456789 FoodLio Grocery8765 Atlanta/ (Formula - Should result in "Food Lion")
Table 2
Column A/ Column B
Row 1: Barnes&Nob/ Barnes & Noble
Row 2: FoodLio/ Food Lion
Row 3: Sunoco/ Gas
Formula = Compare the cell in table A to table B, identify the line in table B that contains similar text, and then return the value in column 2 of table B. So, in the example above, the formula would take the value in Table A, Row 1 (Transaction 123456 Barnes&Nob Site 98765 Los Angelos), find the value in Table B, Column 1 that has a value contained in the referenced cell (Barnes&Nob) and returns the corresponding cell value from Table B, Column 2 as the final response (Barnes & Noble).
So I started with this as a base but don't know where to go with it - =IF(ISNUMBER(SEARCH("dog",A1)),"dog",""). The issue is that as you can imagine, there are tons of values in column 1 of Table A and in Table B, so no way to be as specific as saying "dog", you have to do some sort of vlookup for a partial, wildcard type match on table B column 1.
Any ideas?