Matching companies

[thesisproject]

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>

 

[pleeseemailme]

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.

 

[thesisproject]

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.

 

[pleeseemailme]

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

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

 

[Aladin Akyurek]

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?

 

[pleeseemailme]

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

=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)

 

[thesisproject]

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

 

[Aladin Akyurek]

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:

=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)))

 

[thesisproject]

Thank you both for the codes. They worked great!

 

[Aladin Akyurek]

Thank you both for the codes. They worked great!

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