Complex Cross Referencing

[Sara_S]

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?

 

[Marcelo Branco]

Hi and welcome to Mr Excel Forum

Maybe....

Table 1 in A1:B5
Table 2 in D1:E4

A B C D E

Header1	Header2		Header3	Header4
Transaction 123456 Barnes&Nob Site 98765 Los Angelos	Barnes & Noble		Barnes&Nob	Barnes & Noble
Transaction 234566 Barnes&Nob Site 98765 New York	Barnes & Noble		FoodLio	Food Lion
Transaction 456789 FoodLio Grocery8765 Atlanta	Food Lion		Sunoco	Gas
Transaction 999999 BlahBlah 8765 BlihBlih	Not availble

<tbody>
</tbody>

Array formula in B2 (Excel 2007 or higher)
=IFERROR(INDEX($E:$E,SMALL(IF(ISNUMBER(MATCH("*"&$D$2:$D$4&"*",A2,0)),ROW($E$2:$E$4)),1)),"Not Available")

confirmed with Ctrl+Shift+Enter
(hold down both Ctrl and Shift keys and hit Enter)

copy down

M.

 

[Aladin Akyurek]

Also, using Marcelo's exhibit:

B2, just enter:

=IFERROR(LOOKUP(9.99999999999999E+307,
  SEARCH(" "&$D$2:$D$4&" "," "&$A2&" "),$E$2:$E$4),"Not available")

If you don't have IFERROR on your system...

=LOOKUP(REPT("z",255),CHOOSE({1,2},"Not available",
  LOOKUP(9.99999999999999E+307,SEARCH(" "&$D$2:$D$4&" "," "&$A2&" "),
   $E$2:$E$4)))