Search two columns

[js_odom]

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]

Maybe something like this. Change ranges to match your two different workbooks. Copy formula down and across as needed.

Excel Workbook
	A	B	C	D	E	F
1	Workbook 1			Workbook 2
2	Company	Contact		Company	Contact
3	COM1	Name1		COM1	Name1
4	COM2	Name2		COM2	Name2	Name4
5	COM3	Name3		COM3	Name3
6	COM2	Name4		COM4
7	COM5	Name5		COM5	Name5
8	COM6	Name6		COM6	Name6
9	COM7	Name7		COM7	Name7
10	COM8	Name8		COM8	Name8
Sheet

 

[Peter_SSs]

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]

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]

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.