Match function or another one?

[Twee]

Hi all,


I just started using using the match formula along with index formula and am enjoying how powerful it is. I am wondering if you could help me figure out if there's another function that would better suited for my need.


I'm trying to figure out what percentile a value belongs in given this information.


5th 10th 15th 20th

Patient Satisfaction 65.65 68.0 70.1 72.8

Death in ICU 0 0 2.0 2.8


The match and index function works great in the patient satisfaction example above(higher percentile deemed more favorable) because a value of 69.8 returns the 10th percentile. The match function does what finds the largest value that is less than or equal to the lookup value, returning 2. Match(69.8,B1:E1,0)


However in the case where death in the ICU (lower percentile deemed more favorable), the same match function returns the 10th percentile for the value of 0 and I was wondering if there's anyway for it to return the smaller percentile if there is another column (Lower is better)that identifies which measure this rule applies to.


Thank you for any help you can lend.

 

[Hui]

Have you tried just moving the match by 1 cell by adding a -1 to the Index:


=Index(B2:E2,1,Match(69.8,B1:E1,1)-1)

 

[Twee]

Hi Hui,


The -1 works great for this scenario but sometimes I have to minus 3 or so and the number could vary for it to work and i guess I could count the number of 0s in a column and ask it to use that column but was wondering if there's something more elegant that i'm missing? Thanks for help.

 

[Hui]

Twee


If you are looking up relative to a value eg: 69.8 there must be a rule for not accepting the corresponding value that is either more or less than the given,


You will need to be able to define what is "More favorable" in a mathematical sense?