Finding closest match in large dataset

[neil_999]

Hi all,

I'm new here and looking some advice on a formula for finding the `best match` in a large dataset. I've attached an example
I have a sheet of 95,000 locations, each are given a score based on certain criteria (listed A-V). I need a formula whereby if a user types in the scores they want to see (in the green cells), cell A12 will tell them the location that best matches their requirements (which is the location that matches each score (values in A-V) within a tolerance of, ideally, but not essentially, 5%). The scores will never be exact, but what I'm looking for is the closest overall match

Due to security restrictions, VBA or Add-ins can't be used

Any help would be gratefully received

Example.xlsx

[CARACALLA]

, Fast answers need clear examples. Post a small Excel sheet (not a picture) showing realistic & representative sample data WITHOUT confidential information (10-20 rows, not thousands...) and some manually calculated results. For a new thread (1st post), scroll to Manage Attachments, otherwise scroll down to GO ADVANCED, click, and then scroll down to MANAGE ATTACHMENTS and click again. Now follow the instructions at the top of that screen.

[TMS]

Please define "the location that best matches their requirements."

[neil_999]

I've added the attachment and been more specific with the requirements

[AliGW]

There is NO explanation of the values in pink. Are these lookup values or expected results? If the latter, how did you arrive at them? If the former, what would the expected results be?

[neil_999]

The % values in pink are the lookup values (against the others). The expected results would be the location number in pink where the lookup values most closely match collectively

[AliGW]

Mmm. I think you need to provide a couple more examples. It would help if you highlighted in the main table which is the 'best fit' in each column and explain how you'd break a tie.

[josephteh]

One way I will do is to find the aggregate of AVERAGE, MAX, MIN and MEDIAN, and match the lowest aggregate to the location. Please refer formulas highlighted in yellow.

[Jacc]

I just did the least square sum thing. No idea how valid that is.

[Jacc]

I ran your method against mine, josephteh. Interestingly they sometimes produce the opposite results. There seems to be almost no correlation between these two methods.

[Jacc]

After googling this for several seconds I realized that weighted least square is probably the way to go. The plain least square that I posted will be dominated by the large numbers and small numbers will be more or less ignored.
Weighting the data should even out that difference. I'll leave this to people who actually knows statistic.

[neil_999]

This is amazing, thank you! Some great ideas

[josephteh]

Jacc's solution is definitely better. Least square or weighted least square is definitely the way to go. Btw, how will you calculate weighted least square, Jacc?

I made a mistake with my method.. ABS should apply to MIN as well.

Actually, Excel has a function for this thing: CORREL.

Here is a Rep for you, Jacc!

[Jacc]

Thanks josephtet! Nice comparison of methods!
As for the weighted least square I don't know how to calculate that. I'm not even sure that is correct.
I was hoping someone with a proper statistical background would show up in this thread and enlighten us.

[Jacc]

I just looked at it once more, your comparison matrix with good match highlighted in green might has me wondering. Should the comparison be made by division instead of subtraction? It would be difficult to match smaller numbers otherwise.

[josephteh]

Here you go, Jacc! Subtraction or division do not seem to make any difference. Better to stick with CORREL or your least square solution.