Finding a closest match with more than one criteria

Hi There,

I'm having a problem and hoping to get some help. I've got the attached data and I want to use a formula to find the closest case (Person_ID) or cases to the ideal. In the attached table it would be Person_ID 1002 but I need to use a formula or something to find.


Thank in advance.

Jayana

Maybe...

Please Login or Register  to view this content.

Ctrl+Shift+Enter not only Enter.

Hello Jayana,

Try this in B14 & copy across.

=INDEX($A$2:$A$6,MATCH(MAX(INDEX((B$2:B$6<B13)*B$2:B$6,0)),B$2:B$6,0))

Perhaps something along the lines of:

Please Login or Register  to view this content.

Note the Array entry requirement. If the Array has been set correctly the above formula will appear encased within {}