Combine 2 tables in Excel if 2 columns match

Ms_Excel

New Member
Joined
Nov 16, 2011
Messages
13
Hi there,

I would like to ask a question: I have two lists/tables in Excel containing:Name of the company, address and so on. I need to add column "email address" to the first list from the 2-nd, criteria: adresses and names from 1 and 2 list are same. The problem here that the names and addresses in the first list are written in capital letters, in second -not. I tried using Match and VLookup,but unfortunatelly it did not work. I would apreciate any help in this matter. This is my last formula:

=IF(AND(MATCH(P8,$B$2:$C$6347,0),MATCH(Q8,$D$2:$E$6347,0)),R8,"") it return n/a...2-nd list is smaller than the first. (first list may contain company with the same name, but different addresses).

Thank you in advance.
 

Some videos you may like

Excel Facts

Which Excel functions can ignore hidden rows?
The SUBTOTAL and AGGREGATE functions ignore hidden rows. AGGREGATE can also exclude error cells and more.

oldrelia23

New Member
Joined
Dec 2, 2010
Messages
25
One way would just be to force the lookup criteria and values for the names column to be lower case in all cases. Perhaps this would work as the formula in the first row for email addresses in the first list:

{=INDEX($H$4:$H$5,MATCH(1,(LOWER($F$4:$F$5)=LOWER($B4))*($G$4:$G$5=$C4),0))}

Where H4:H5 is the 2nd list email column, F4:F5 is the 2nd list name column, and G4:G5 is the second list address column. B4 is 1st list name, C5 is the 1st list address.
 

Ms_Excel

New Member
Joined
Nov 16, 2011
Messages
13
hello again and thank u all for your replies...sorry for not posting the sample tables. Please see below for your reference (could not find where to attach file or image). In table one the column "email address" is created by me, where I would like to see email addresses from table 2, with the criteria that the names of the company and the addresses in 2 tables should be same.

Table1:

<table width="358" border="0" cellpadding="0" cellspacing="0"><col style="width: 75pt;" width="100"> <col style="width: 104pt;" width="138"> <col style="width: 90pt;" width="120"> <tbody><tr style="height: 15pt;" height="20"> <td class="xl65" style="height: 15pt; width: 75pt;" width="100" height="20">Name</td> <td class="xl65" style="border-left: medium none; width: 104pt;" width="138"> address</td> <td class="xl65" style="border-left: medium none; width: 90pt;" width="120">email address</td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl64" style="height: 15pt; border-top: medium none;" height="20">COMPANY1</td> <td class="xl64" style="border-top: medium none; border-left: medium none;"> SYDNEY</td> <td class="xl64" style="border-top: medium none; border-left: medium none;"> </td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl64" style="height: 15pt; border-top: medium none;" height="20">COMPANY1</td> <td class="xl64" style="border-top: medium none; border-left: medium none;">PERTH</td> <td class="xl64" style="border-top: medium none; border-left: medium none;"> </td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl64" style="height: 15pt; border-top: medium none;" height="20">COMPANY2</td> <td class="xl64" style="border-top: medium none; border-left: medium none;">MELBOURNE</td> <td class="xl64" style="border-top: medium none; border-left: medium none;"> </td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl64" style="height: 15pt; border-top: medium none;" height="20">COMPANY3</td> <td class="xl64" style="border-top: medium none; border-left: medium none;">HOBART</td> <td class="xl64" style="border-top: medium none; border-left: medium none;"> </td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl64" style="height: 15pt; border-top: medium none;" height="20">COMPANY4</td> <td class="xl64" style="border-top: medium none; border-left: medium none;">BRISBANE</td> <td class="xl64" style="border-top: medium none; border-left: medium none;"> </td> </tr> </tbody></table>
Table2:

<table width="298" border="0" cellpadding="0" cellspacing="0"><col style="width: 59pt;" width="79"> <col style="width: 68pt;" width="91"> <col style="width: 96pt;" width="128"> <tbody><tr style="height: 15pt;" height="20"> <td class="xl67" style="height: 15pt; width: 59pt;" width="79" height="20">name</td> <td class="xl67" style="border-left: medium none; width: 68pt;" width="91"> address</td> <td class="xl67" style="border-left: medium none; width: 96pt;" width="128"> email address</td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl65" style="height: 15pt; border-top: medium none;" height="20">Company1</td> <td class="xl65" style="border-top: medium none; border-left: medium none;"> Sydney</td> <td class="xl66" style="border-top: medium none; border-left: medium none;"> test@yahoo.com</td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl65" style="height: 15pt; border-top: medium none;" height="20">Company2</td> <td class="xl65" style="border-top: medium none; border-left: medium none;"> Melbourne</td> <td class="xl66" style="border-top: medium none; border-left: medium none;"> ggg@hotmail.com</td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl65" style="height: 15pt; border-top: medium none;" height="20">Company3</td> <td class="xl65" style="border-top: medium none; border-left: medium none;"> Hobart</td> <td class="xl66" style="border-top: medium none; border-left: medium none;"> mmm@test.com</td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl65" style="height: 15pt; border-top: medium none;" height="20">Company4</td> <td class="xl65" style="border-top: medium none; border-left: medium none;"> Brisbane</td> <td class="xl66" style="border-top: medium none; border-left: medium none;"> jut@aaanet.com</td> </tr> </tbody></table>
 

Ms_Excel

New Member
Joined
Nov 16, 2011
Messages
13

ADVERTISEMENT

Oldrelia, I just tried your methos but the result is unfortunately N/A:(

Anyway,thank you for thrying to help me:)
 

Dannyh1

Well-known Member
Joined
Nov 18, 2009
Messages
1,144
An easy solution would be to combine columns in both lists & do a vlookup that way
 

Aladin Akyurek

MrExcel MVP
Joined
Feb 14, 2002
Messages
85,201

ADVERTISEMENT

hello again and thank u all for your replies...sorry for not posting the sample tables. Please see below for your reference (could not find where to attach file or image). In table one the column "email address" is created by me, where I would like to see email addresses from table 2, with the criteria that the names of the company and the addresses in 2 tables should be same.

Table1:

<TABLE border=0 cellSpacing=0 cellPadding=0 width=358><COLGROUP><COL style="WIDTH: 75pt" width=100><COL style="WIDTH: 104pt" width=138><COL style="WIDTH: 90pt" width=120><TBODY><TR style="HEIGHT: 15pt" height=20><TD style="WIDTH: 75pt; HEIGHT: 15pt" class=xl65 height=20 width=100>Name</TD><TD style="BORDER-LEFT: medium none; WIDTH: 104pt" class=xl65 width=138>address</TD><TD style="BORDER-LEFT: medium none; WIDTH: 90pt" class=xl65 width=120>email address</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="HEIGHT: 15pt; BORDER-TOP: medium none" class=xl64 height=20>COMPANY1</TD><TD style="BORDER-LEFT: medium none; BORDER-TOP: medium none" class=xl64>SYDNEY</TD><TD style="BORDER-LEFT: medium none; BORDER-TOP: medium none" class=xl64></TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="HEIGHT: 15pt; BORDER-TOP: medium none" class=xl64 height=20>COMPANY1</TD><TD style="BORDER-LEFT: medium none; BORDER-TOP: medium none" class=xl64>PERTH</TD><TD style="BORDER-LEFT: medium none; BORDER-TOP: medium none" class=xl64></TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="HEIGHT: 15pt; BORDER-TOP: medium none" class=xl64 height=20>COMPANY2</TD><TD style="BORDER-LEFT: medium none; BORDER-TOP: medium none" class=xl64>MELBOURNE</TD><TD style="BORDER-LEFT: medium none; BORDER-TOP: medium none" class=xl64></TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="HEIGHT: 15pt; BORDER-TOP: medium none" class=xl64 height=20>COMPANY3</TD><TD style="BORDER-LEFT: medium none; BORDER-TOP: medium none" class=xl64>HOBART</TD><TD style="BORDER-LEFT: medium none; BORDER-TOP: medium none" class=xl64></TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="HEIGHT: 15pt; BORDER-TOP: medium none" class=xl64 height=20>COMPANY4</TD><TD style="BORDER-LEFT: medium none; BORDER-TOP: medium none" class=xl64>BRISBANE</TD><TD style="BORDER-LEFT: medium none; BORDER-TOP: medium none" class=xl64></TD></TR></TBODY></TABLE>
Table2:

<TABLE border=0 cellSpacing=0 cellPadding=0 width=298><COLGROUP><COL style="WIDTH: 59pt" width=79><COL style="WIDTH: 68pt" width=91><COL style="WIDTH: 96pt" width=128><TBODY><TR style="HEIGHT: 15pt" height=20><TD style="WIDTH: 59pt; HEIGHT: 15pt" class=xl67 height=20 width=79>name</TD><TD style="BORDER-LEFT: medium none; WIDTH: 68pt" class=xl67 width=91>address</TD><TD style="BORDER-LEFT: medium none; WIDTH: 96pt" class=xl67 width=128>email address</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="HEIGHT: 15pt; BORDER-TOP: medium none" class=xl65 height=20>Company1</TD><TD style="BORDER-LEFT: medium none; BORDER-TOP: medium none" class=xl65>Sydney</TD><TD style="BORDER-LEFT: medium none; BORDER-TOP: medium none" class=xl66>test@yahoo.com</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="HEIGHT: 15pt; BORDER-TOP: medium none" class=xl65 height=20>Company2</TD><TD style="BORDER-LEFT: medium none; BORDER-TOP: medium none" class=xl65>Melbourne</TD><TD style="BORDER-LEFT: medium none; BORDER-TOP: medium none" class=xl66>ggg@hotmail.com</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="HEIGHT: 15pt; BORDER-TOP: medium none" class=xl65 height=20>Company3</TD><TD style="BORDER-LEFT: medium none; BORDER-TOP: medium none" class=xl65>Hobart</TD><TD style="BORDER-LEFT: medium none; BORDER-TOP: medium none" class=xl66>mmm@test.com</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="HEIGHT: 15pt; BORDER-TOP: medium none" class=xl65 height=20>Company4</TD><TD style="BORDER-LEFT: medium none; BORDER-TOP: medium none" class=xl65>Brisbane</TD><TD style="BORDER-LEFT: medium none; BORDER-TOP: medium none" class=xl66>jut@aaanet.com</TD></TR></TBODY></TABLE>
Let A1:C6 house Table 1 and E1:G5 house Table 2...

C2, control+shift+enter, not just enter, and copy down:
Rich (BB code):
=IFERROR(
 INDEX($G$2:$G$5,MATCH(1,{0}+IF($E$2:$E$5=$A2,
  IF($F$2:$F$5=$B2,1)),0)),"Not Found")

If not on Excel 2007 or later, try...

C2, control+shift+enter, not just enter, and copy down:
Rich (BB code):
=LOOKUP(REPT("z",255),CHOOSE({1,2},"Not Found",
 INDEX($G$2:$G$5,MATCH(1,{0}+IF($E$2:$E$5=$A2,
  IF($F$2:$F$5=$B2,1)),0))))
 

Ms_Excel

New Member
Joined
Nov 16, 2011
Messages
13
Let A1:C6 house Table 1 and E1:G5 house Table 2...

C2, control+shift+enter, not just enter, and copy down:
Rich (BB code):
=IFERROR(
 INDEX($G$2:$G$5,MATCH(1,{0}+IF($E$2:$E$5=$A2,
  IF($F$2:$F$5=$B2,1)),0)),"Not Found")
If not on Excel 2007 or later, try...

C2, control+shift+enter, not just enter, and copy down:
Rich (BB code):
=LOOKUP(REPT("z",255),CHOOSE({1,2},"Not Found",
 INDEX($G$2:$G$5,MATCH(1,{0}+IF($E$2:$E$5=$A2,
  IF($F$2:$F$5=$B2,1)),0))))
Thanks a lot for the replies,guys. I just tried your method, Aladin, for the C2 it was ok, found emaild address,when I copied the formula down, it was writen" not found". I tried "ctrl,shift,esnter" but nothing happened and i used 2-nd code just inserting it. My Excel version is 2007...And another question, what if the values in both tables are not sorted in ascending order,vlookup would not work, is not it? what should i do in that case?
 

Aladin Akyurek

MrExcel MVP
Joined
Feb 14, 2002
Messages
85,201
Thanks a lot for the replies,guys. I just tried your method, Aladin, for the C2 it was ok, found emaild address,when I copied the formula down, it was writen" not found". I tried "ctrl,shift,esnter" but nothing happened and i used 2-nd code just inserting it. My Excel version is 2007...And another question, what if the values in both tables are not sorted in ascending order,vlookup would not work, is not it? what should i do in that case?

Both formulas will work on 2007.

Type or copy the formula, apply control+shift+enter, then drag down the formula.
 

Ms_Excel

New Member
Joined
Nov 16, 2011
Messages
13
that worked :) Thank you so so much, I will try to apply it tomorrow for the real tables:)Thanks once again!!!
 

Watch MrExcel Video

Forum statistics

Threads
1,123,318
Messages
5,600,924
Members
414,416
Latest member
Nobu

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
Top