match with array in first argument?

P

Pasta Go

New Member
Joined
Oct 8, 2008
Messages
2
Greetings

I have two tables that I'm trying to pass information from one to the other -

Table A -
ShortName CustNumber
ABC 123
DEF 543
HIJL 443
...
...

Table B -
LongName Amount CustNumber
DEF Incorporated $343 ????
State of ABC $443 ????
...
...

I'd like to fill in CustNumber in Table B if there's a match between the ShortName and LongName (ie: if the ShortName is a substring of the Longname). Alas, I've tried without success of using match() or vlookup(). Any help would be appreciated.
 

Excel Facts

How to calculate loan payments in Excel?
Use the PMT function: =PMT(5%/12,60,-25000) is for a $25,000 loan, 5% annual interest, 60 month loan.
Sort by date Sort by votes
Pasta Go said:
Greetings

I have two tables that I'm trying to pass information from one to the other -

Table A -
ShortName CustNumber
ABC 123
DEF 543
HIJL 443
...
...

Table B -
LongName Amount CustNumber
DEF Incorporated $343 ????
State of ABC $443 ????
...
...

I'd like to fill in CustNumber in Table B if there's a match between the ShortName and LongName (ie: if the ShortName is a substring of the Longname). Alas, I've tried without success of using match() or vlookup(). Any help would be appreciated.
Click to expand...

<TABLE style="WIDTH: 414pt; BORDER-COLLAPSE: collapse" cellSpacing=0 cellPadding=0 width=552 border=0 x:str><COLGROUP><COL style="WIDTH: 60pt; mso-width-source: userset; mso-width-alt: 2925" width=80><COL style="WIDTH: 65pt; mso-width-source: userset; mso-width-alt: 3181" width=87><COL style="WIDTH: 48pt" span=2 width=64><COL style="WIDTH: 82pt; mso-width-source: userset; mso-width-alt: 3986" width=109><COL style="WIDTH: 46pt; mso-width-source: userset; mso-width-alt: 2230" width=61><COL style="WIDTH: 65pt; mso-width-source: userset; mso-width-alt: 3181" width=87><TBODY><TR style="HEIGHT: 12.75pt" height=17><TD class=xl25 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; WIDTH: 60pt; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" width=80 height=17>ShortName</TD><TD class=xl25 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext 0.5pt solid; BORDER-LEFT: windowtext; WIDTH: 65pt; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent" width=87>CustNumber</TD><TD class=xl25 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext 0.5pt solid; BORDER-LEFT: windowtext; WIDTH: 48pt; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent" width=64></TD><TD class=xl25 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext 0.5pt solid; BORDER-LEFT: windowtext; WIDTH: 48pt; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent" width=64></TD><TD class=xl25 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext 0.5pt solid; BORDER-LEFT: windowtext; WIDTH: 82pt; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent" width=109>LongName</TD><TD class=xl25 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext 0.5pt solid; BORDER-LEFT: windowtext; WIDTH: 46pt; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent" width=61>Amount</TD><TD class=xl25 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext 0.5pt solid; BORDER-LEFT: windowtext; WIDTH: 65pt; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent" width=87>CustNumber</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl26 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext 0.5pt solid; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" height=17>ABC</TD><TD class=xl26 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent" align=right x:num>123</TD><TD class=xl26 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent"> </TD><TD class=xl26 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent"> </TD><TD class=xl26 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent">DEF Incorporated</TD><TD class=xl26 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent" align=right x:num>343</TD><TD class=xl26 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent" align=right x:num x:fmla='=LOOKUP(9.99999999999999E+307,SEARCH(" "&$A$2:$A$4&" "," "&E2&" "),$B$2:$B$4)'>543</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl26 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext 0.5pt solid; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" height=17>DEF</TD><TD class=xl26 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent" align=right x:num>543</TD><TD class=xl26 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent"> </TD><TD class=xl26 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent"> </TD><TD class=xl24 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent">State of ABC</TD><TD class=xl26 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent" align=right x:num>443</TD><TD class=xl26 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent" align=right x:num x:fmla='=LOOKUP(9.99999999999999E+307,SEARCH(" "&$A$2:$A$4&" "," "&E3&" "),$B$2:$B$4)'>123</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl26 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext 0.5pt solid; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" height=17>HIJL</TD><TD class=xl26 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent" align=right x:num>443</TD><TD class=xl26 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent"> </TD><TD class=xl26 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent"> </TD><TD class=xl26 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent"> </TD><TD class=xl26 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent"> </TD><TD class=xl26 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent"> </TD></TR></TBODY></TABLE>

Table A is in A1:B4, Table B in E1:G3...

The formula in G2 is:

=LOOKUP(9.99999999999999E+307,SEARCH(" "&$A$2:$A$4&" "," "&E2&" "),$B$2:$B$4)

which is copied down.
 
Upvote 0
Aladin - your solution worked like a charm. I'm confused as to how it's working...when I tried to run just the SEARCH part, I'm getting #value errors. And why is is the additional " " needed?
 
Upvote 0
Pasta Go said:
Aladin - your solution worked like a charm. I'm confused as to how it's working...when I tried to run just the SEARCH part, I'm getting #value errors. And why is is the additional " " needed?
Click to expand...

The SEARCH bit in

=LOOKUP(9.99999999999999E+307,SEARCH(" "&$A$2:$A$4&" "," "&E2&" "),$B$2:$B$4)

tests whether any string in A2:A4 is in E2 and if so, the LOOKUP expression returns the number that corresponds to that string.

Evaluations...

LOOKUP(9.99999999999999E+307,SEARCH({" ABC ";" DEF ";" HIJL "}," DEF Incorporated "),$B$2:$B$4)

With SEARCH, stringg in A2:A4 and E2 are surrounded with a space in order to prevent a match between, say, AB and ABC.

LOOKUP(9.99999999999999E+307,{#VALUE!;1;#VALUE!},$B$2:$B$4)

The first #VALUE! means that " ABC " is not in " DEF Incorporated ", 1 means that " DEF " matches the substring " DEF " of " DEF Incorporated " starting at position 1, and " HIJL " is not there as the match attempt yields #VALUE!.

LOOKUP(9.99999999999999E+307,{#VALUE!;1;#VALUE!},{123;543;443})

Here LOOKUP attempts to the last numeric value in {#VALUE!;1;#VALUE!} and, if successful, retrieves from {123;543;443} the corresponding number: In this case, it's of course 543.
 
Upvote 0
You must log in or register to reply here.

Similar threads

A
  • Question
Compare strings with substrings in excel
Replies
2
Views
802
gromexe
G
A
  • Question
Matching and Comparing data from sheet1 to sheet2
Replies
1
Views
462
appreciateyourhelp
A
C
  • Question
INDEX & MATCH functions with VLOOKUP function as {array} argument (Excel 2013, Windows 8.1)
Replies
3
Views
642
Colo_Mike
C
C
  • Question
How to compare two lists of text and provide a matching level in percentage
Replies
1
Views
5K
VoG
VoG
B
  • Question
Multi-Argument Data Comparison using no formulas - Is this possible?
Replies
0
Views
493
byoung70104
B

Forum statistics

Threads
1,215,067
Messages
6,122,949
Members
449,095
Latest member
nmaske

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top