Simple pair matching using dynamic criteria

Hi,

I want to conduct simple pair matching in Excel.

I have a group of companies (191) which have received a "treatment", and I want to match each company to exactly one other company from a larger group with 1286 companies.

Each company should be matched on the following criteria in this order: 1-digit SIC code (ranges 0-9), sales in the range 70% to 130% (ranges 0-999999) of the treatment firm and the lowest book-to-market ratio (ranges 0.0 to 999). For both companies, these values are arranged in 3 separate columns, such that I have 3x191 and 3x1286.

For each of the 191 entries, I would like to loop through the range of the first row that contains all matching SIC-codes, then within that range find all entries which are in the range of sales and then find the book-to-market ratio closest to the one of the treatment firm. The formula should return the value from the fourth column, which contains a unique identifier for the matched firm.

Will you please attach a sample Excel workbook? We are not able to work with or manipulate a picture of one and nobody wants to have to recreate your data from scratch.

1. Make sure that your sample data are REPRESENTATIVE of your real data. The use of unrepresentative data is very frustrating and can lead to long delays in reaching a solution.

2. Make sure that your desired results are also shown (mock up the results manually).

3. Make sure that all confidential data is removed or replaced with dummy data first (e.g. names, addresses, E-mails, etc.).

4. Try to avoid using merged cells as they cause lots of problems.

Unfortunately the attachment icon doesn't work at the moment, so to attach an Excel file you have to do the following: just before posting, scroll down to Go Advanced and then scroll down to Manage Attachments. Now follow the instructions at the top of that screen.

Please pay particular attention to point 2 (above): without an idea of your intended outcomes, it is often very difficult to offer appropriate advice.

Not that simple!!!

An array formula, copied down:

Formula:

=IFERROR(INDEX($F$2:$F$1286,MATCH(MIN(IF($C$2:$C$1286=AJ2,IF($D$2:$D$1286>=0.7*AK2,IF($D$2:$D$1286<=1.3*AK2,ABS($E$2:$E$1286-AL2))))),ABS($E$2:$E$1286-AL2),0)),"")

Array Formulae are a little different from ordinary formulae in that they MUST be confirmed in the FIRST CELL ONLY by pressing CTRL+SHIFT+ENTER to activate the array, not just ENTER. After that, the array can be dragged down as normal, to cover the desired range.

You will know the array is active when you see curly brackets { } - or "curly braces" for those of you in the USA, or "flower brackets" for those of you in India - appear around the outside of your formula. If you do not use CTRL+SHIFT+ENTER you will (almost always) get an error message or an incorrect answer. Press F2 on that cell and try again.

Don't type the curly brackets yourself - it won't work...

If copying the formula from the post (rather than from the attached sheet) remember to change , into ; to meet your regional settings.

You're welcome.

---

If that takes care of your original question, please select "Thread Tools" from the menu link above and mark this thread as SOLVED.

It'd also be appreciated if you were to click the Add Reputation button at the foot of any of the posts of all members who helped you reach a solution.