# Search two columns

#### js_odom

##### New Member
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
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
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
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
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.