How do I look up values from a range of values?

[Guest]

Hi,
I need to look up values from a range of numbers, like between. Can somebody
help me with it?

For example one worksheet Cell A1 I have number 2500. In another worksheet
I have 2 rows data like the following:

20th %tile 21st %tile 22nd %tile 23th %tile 24th %tile 25th
%tile ...........90th
1200 1500 1700 1700 1998 2700
...........8885

In this case, the formula cell should return me the 24th %tile sine 2500 is
between 1998 and 2700. Keep in mind some cells have the same number like 22nd
and 23th are the same and since I have data from 20th all the way to 90th, I
can NOT write if function for 70 some times.

I really appreciate anybody can help me!

 

[Guest]

First, the formula will be easier to create if you arrange your data this way:

1200 1500 1700 1700 1998 2700
20th %tile 21st %tile 22nd %tile 23th %tile 24th %tile 25th

I tested this with those values in cells D1:I2

A1: 2500
B1: =HLOOKUP(A1,$D$1:$I$2,2,1)

Is that something you can work with?

 

[Guest]

assuming percents are in row 1 and data is in row 2
try
=index(Sheet2!A1,1,Match(Sheet1!A1,Sheet2!A2A2))