Search two columns

js_odom

New Member
Joined
Dec 18, 2017
Messages
8
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.
 

Excel Facts

Show numbers in thousands?
Use a custom number format of #,##0,K. Each comma after the final 0 will divide the displayed number by another thousand
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
3COM1Name1COM1Name1
4COM2Name2COM2Name2Name4
5COM3Name3COM3Name3
6COM2Name4COM4
7COM5Name5COM5Name5
8COM6Name6COM6Name6
9COM7Name7COM7Name7
10COM8Name8COM8Name8
Sheet
 
Upvote 0
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))),"")
 
Upvote 0
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.
 
Upvote 0
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.
 
Upvote 0

Forum statistics

Threads
1,213,546
Messages
6,114,256
Members
448,558
Latest member
aivin

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