• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Matching records by multiple criteria

W

Wojciech

New Member
I presume that this will require an array formula of sorts... I hope to resolve it without a macro, mostly because I wouldn't know where to start.

See attached scenario.
It's a standard problem for me by now that I like Index&Match, but it settles for the first record it finds. Right now I need it to go further look at all records that suit the criteria then pick one that's the most probable match based on the number (in this scenario, test scores, for me it's financial, might be an added difficulty that the numbers can be negative I guess...).

Note: The match doesn't actually have to be the most accurate, just within the set level, say 10% of the figure I'm looking at. In the scenario given, I wouldn't be particularly fussy if the application threw back someone just like Ken but with a score of 76 instead of 77, just so long as it throws back someone that's within the 75-85 bracket (based on Kevin's score of 80).
 

Attachments

  • Chandoo Example.xlsx
    9.8 KB · Views: 9
Hi Narayan,

Yes! That's it. But could I please ask for an explanation of this piece:
(ABS($E$2:$E$5 - E2) <= Tolerance)

The tolerance bit, is that a note? How??? How did this note not throw the formulae into errors?

Thank you so much for the ABS function too, I never seen it before and it's something I can abs-olutely use.
 
Hi ,

Tolerance is a named range ( you can open the Name Manager to look at it ) ; I have put the number 5 in it ; you can change this value to any other value , say 2 if you want.

Narayan
 
You must log in or register to reply here.
Back
Top