# Complex Cross Referencing

#### Sara_S

##### New Member
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?

### Excel Facts

Square and cube roots
The =SQRT(25) is a square root. For a cube root, use =125^(1/3). For a fourth root, use =625^(1/4).

#### Marcelo Branco

##### MrExcel MVP
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

##### MrExcel MVP
Also, using Marcelo's exhibit:

B2, just enter:
Rich (BB code):
``````=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...
Rich (BB code):
``````=LOOKUP(REPT("z",255),CHOOSE({1,2},"Not available",
LOOKUP(9.99999999999999E+307,SEARCH(" "&\$D\$2:\$D\$4&" "," "&\$A2&" "),
\$E\$2:\$E\$4)))``````

Replies
0
Views
175
Replies
2
Views
246
Replies
0
Views
181
Replies
1
Views
175
Replies
9
Views
456

Threads
1,109,132
Messages
5,527,029
Members
409,737
Latest member
shanghity