complicated lookup using functions

[hvalhund]

Hello again,

is it possible to search for a value based on a certain conditions bottom up?

I am trying to search an index based on input percentile.

e.g. for 5%, it will be "w index"=22 (because it is closest to but not bigger than 5%) and "e index"=5, because that is the "e index" value with percentile closest to (but not bigger than) 5%.

I could write a code to handle this unless there is a more elegant way - and I am sure there must be!

Thanks in advance

% e index w index

0.18% 0 0
0.36% 0 1
0.53% 0 2
0.71% 0 3
0.89% 1 0
1.07% 0 4
1.25% 0 5
1.43% 0 6
1.60% 0 7
1.78% 2 0
1.96% 0 8
2.14% 0 9
2.32% 0 10
2.50% 0 11
2.67% 3 0
2.85% 4 0
3.03% 5 0
3.21% 0 12
3.39% 0 13
3.57% 0 14
3.74% 0 15
3.92% 0 16
4.10% 0 17
4.28% 0 18
4.46% 0 19
4.63% 0 20
4.81% 0 21
4.99% 0 22
5.17% 0 23

[Kaper]

First part is clear.
=INDEX(C2:C30,MATCH(5%,A2:A30,1))
Would do.
But second - you shall explain a bit more in detail. And please read http://www.excelforum.com/forum-rule...rum-rules.html especially notes on attaching files. My favourite one is:

Post a WORKBOOK. Nobody wants to type data from a picture or paste text from your post into a spreadsheet as a prelude to helping.

[hvalhund]

Thanks Kaper,

spreadsheet attached.

Some background information first - the attached table represents easterly and westerly deviations from a prescribed centreline (negative deviations towards west), sorted ascending based on absolute value of deviation from the centreline. For both eastern and western directions I would like to calculate the maximum deviation appropriate to a selected percentile, e.g. for 25% percentile it would mean that maximum westerly deviation for the first 25% of records was (-542.019) and for easterly deviation it was 473.716 .
We also need to approximate the percentiles in column "A" as these will be recalculated based on different input data. Moreover, it is not always truth that our westerly deviation we are looking for is closer to the pre-set percentile we are using than easterly deviation - this prevents us from using your solution but thanks anyway.
I tried to play with array formulas in the worksheet but could not get it done properly. Maybe I am missnig something - cells N4 and J10.

Thanks for your efforts

[Kaper]

If I understood description right (and seems I did because results are right) you could use array formula (comitted with Ctrl+Shift+Enter):

Please Login or Register  to view this content.

and the same but with MAX for east.
see cells L4 and N4. Columns K and M are no longer needed. Or if you need this for some purpose you could use for M4:

Please Login or Register  to view this content.

and similar for K4

[hvalhund]

Many thanks Kaper! This did the trick.