Search two columns

js_odom

New Member
Joined
Dec 18, 2017
Messages
6
I have two workbooks with a company name column in both, I need to search workbook 1 for matching company name and if it's a match I need the name from the contact column in workbook 1 added to workbook 2. I can do this with INDEX/MATCH, but my problem is I have rows under company name that have the same company name but the contact name is different. Currently if the company name appears more than once I get the same contact name that is in the first instance of that company.
 

AhoyNC

Well-known Member
Joined
Oct 10, 2011
Messages
4,307
Maybe something like this. Change ranges to match your two different workbooks. Copy formula down and across as needed.
Excel Workbook
ABCDEF
1Workbook 1Workbook 2
2CompanyContactCompanyContact
3COM1Name1COM1
4COM2Name2COM2Name2Name4
5COM3Name3COM3Name3
6COM2Name4COM4
7COM5Name5COM5Name5
8COM6Name6COM6Name6
9COM7Name7COM7Name7
10COM8Name8COM8Name8
Sheet
 

Peter_SSs

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
41,684
Office Version
365
Platform
Windows
Maybe something like this.
Shouldn't that E3 formula you posted be this?

=IFERROR(INDEX($B$3:$B$10,AGGREGATE(15,6,(ROW($A$3:$A$10)-ROW($A$3)+1)/($A$3:$A$10=$D3),COLUMNS($D$2:D2))),"")
 

jasonb75

Well-known Member
Joined
Dec 30, 2008
Messages
6,883
Office Version
2019
Platform
Windows
Perhaps you could also reduce the number of calculations steps in the array by using the entire column as the index range.

=IF(D3="","",IFERROR(INDEX($B:$B,AGGREGATE(15,6,ROW($A$3:$A$10)/($A$3:$A$10=$D3),COLUMNS($E$3:E3))),""))

The additional logical test bypasses the array after the first blank in each row as opposed to calculating it then catching the error it returns.
 

AhoyNC

Well-known Member
Joined
Oct 10, 2011
Messages
4,307
Peter_SSs
Thanks. Yes, you are right that should be $D3. I had moved some columns around before posting and didn't catch the error in the cell.
 

Forum statistics

Threads
1,077,674
Messages
5,335,605
Members
399,028
Latest member
greyland

Some videos you may like

This Week's Hot Topics

Top