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.
 

Excel Facts

Who is Mr Spreadsheet?
Author John Walkenbach was Mr Spreadsheet until his retirement in June 2019.
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.
 
Upvote 0
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>
 
Upvote 0
Oldrelia, I just tried your methos but the result is unfortunately N/A:(

Anyway,thank you for thrying to help me:)
 
Upvote 0
An easy solution would be to combine columns in both lists & do a vlookup that way
 
Upvote 0
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))))
 
Upvote 0
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?
 
Upvote 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?

Both formulas will work on 2007.

Type or copy the formula, apply control+shift+enter, then drag down the formula.
 
Upvote 0
that worked :) Thank you so so much, I will try to apply it tomorrow for the real tables:)Thanks once again!!!
 
Upvote 0

Forum statistics

Threads
1,213,546
Messages
6,114,255
Members
448,556
Latest member
peterhess2002

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