# Matching companies

#### thesisproject

##### New Member
I would like to match each company in list A to a singular company from list B. List A has about 354 companies and list B has 2,988 companies. Each company has an industry code (a.k.a. SIC) and a size amount. I would like to match each company in list A to the company in list B with the closest industry code and size amount. I would like to first match the company by industry code and then match the company to the closest size amount from that list of a particular industry code. If the size amount is not available (N/A) the companies would only need to be matched by industry code. The end result would be one company from list B matched to each company from list A.

Thank you so much.

Here is an example with a header (the letters in the second row indicate column). Sorry, this format may be a little confusing, please let me know if it needs to be altered to complete the matching task.

Example:
 LIST B LIST B LIST B Matched Company LIST A LIST A LIST A A C F G H L M SIC (Industry code) Name Size Matched Company Name SIC (Industry code) Size 4656 AA 123.4 X Q 3434 1235.564 3434 Y 95.13 AA R 4656 N/A 3434 Z 892.134 Continued... Cont. Cont. 3434 X 1240.23 1234 BB 67.3 4657 CC 12.9 Cont. Cont. Cont.

<tbody>
</tbody>

### Excel Facts

Why are there 1,048,576 rows in Excel?
The Excel team increased the size of the grid in 2007. There are 2^20 rows and 2^14 columns for a total of 17 billion cells.
Are the companies in list B sorted in any kind of order? Or are they just randomly thrown in there? If the list could be sorted smallest to largest by column A first and then by column F I could write a formula that will work.

Are the companies in list B sorted in any kind of order? Or are they just randomly thrown in there? If the list could be sorted smallest to largest by column A first and then by column F I could write a formula that will work.

Great! The list is sorted from smallest to largest by Column A and then by Column F. Thank you.

Anyway, if that is the case see if this will work:

Code:
``=OFFSET(\$C\$3,MATCH(L4,(OFFSET(\$A\$3,MATCH(L4,\$A\$4:\$A\$17000,0),5,COUNTIF(\$A\$4:\$A\$17000,L4),1)),1)+MATCH(L4,\$A\$4:\$A\$17000,0)-1,0)``

Place in cell G4 and copy down (or up) as needed.

Last edited:
So, we need to match SIC in L against SIC in A and Size in M against Size in F (a closest match) and return a result from Name in C, right?

Sorry, just realized I didn't account for no-matches. Try

Code:
``=OFFSET(\$C\$3,IFERROR(MATCH(M4,(OFFSET(\$A\$3,MATCH(L4,\$A\$4:\$A\$17000,0),5,COUNTIF(\$A\$4:\$A\$17000,L4),1)),1)+MATCH(M4,\$F\$4:\$F\$17000,1)-1,MATCH(L4,\$A\$4:\$A\$17000,0)),0)``

So, we need to match SIC in L against SIC in A and Size in M against Size in F (a closest match) and return a result from Name in C, right?

Exactly

The ranges follow your exhibit and the formula pretends as if the record is in row 4 (all this should be easily adaptable).

G4, control+shift+enter, not just enter, and copy down:
Rich (BB code):
``````=IF(ISNUMBER(M4),INDEX(\$C\$4:\$C\$10,
MATCH(MIN(ABS(IF(\$A\$4:\$A\$10=\$L4,\$F\$4:\$F\$10,9.99E+307)-M4)),
ABS(IF(\$A\$4:\$A\$10=\$L4,\$F\$4:\$F\$10,9.99E+307)-M4),0)),
INDEX(\$C\$4:\$C\$10,MATCH(L4,\$A\$4:\$A\$10,0)))``````

Thank you both for the codes. They worked great!

Thank you both for the codes. They worked great!

You are welcome. The one I suggested does not require sorting...

Replies
0
Views
46
Replies
1
Views
140
Replies
0
Views
463
Replies
2
Views
370
Replies
1
Views
977

1,202,977
Messages
6,052,887
Members
444,608
Latest member
Krunal_Shah

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

### Which adblocker are you using?

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

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