Complex Cross Referencing

Sara_S

New Member
Joined
Sep 14, 2012
Messages
1
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?
 

Some videos you may like

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
Joined
Aug 23, 2010
Messages
16,393
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
Joined
Feb 14, 2002
Messages
85,192
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)))
 

Watch MrExcel Video

Forum statistics

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

This Week's Hot Topics

Top