Matching with multiple criteria/dynamic range

[Timgis]

Hello,

I have a sample of firms ('the sample firms') for which I've gathered firm size and the so called book-to-market ratio. I also have a collection of other firms ('the matching firms'). These matching firms are to be matched to each sample firm. One matching firm can be matched to an unlimited number of sample firms.

The criteria is: a matching firm needs to have a size of 70-130% of that of the sample firm. Of all the firms that are sized within 70-130% of the sample firm, I want to pick the one with the closest book-to-market ratio (its also OK to match with e.g. the smallest book to market ratio that is greater or equal to the sample firm's size (match type -1 or 1).

I would greatly appreciate any input on how to manage this matching process! My main problem is that I don't know how to make the range dynamic, as there are consistently new firms that can be matched (as sample firms differ in sizes, the 70-130% selection criteria will yield different compatible matching firms each time, obviously).

Thanks!

[Glenn Kennedy]

Hi there... and welcome to the Excel Forum. Please attach a sample workbook. Make sure there is enough data to demonstrate your need. Make sure your desired results are shown, mock them up manually if necessary. Remember to remove ALL confidential information first!!!

Click on GO ADVANCED and use the paperclip icon to open the upload window.

View Pic

[Nisha Dhawan]

Hi Timgis,

Please attach the Excel file, it would be easier to understand.


Thanks
Nisha

[Timgis]

Thanks for the advice, I've now created a dummy workbook that I hope shows what kind of procedure I am talking about. Please find the workbook attached. I've tried to show the steps that I would like to automatize in Excel.

Let me know if I can make anything clearer!