I have a range of numbers - effect sizes AD2:AD47. In another column AP, I
want write a formula that will look up the percentile conversion within the
same worksheet. The percentile conversion list the estimate effect size in
Column A - AQ2:AQ22 and then the percentile is in AR2:AR22. So in other
words, if the effect size is 0.4 in AD2 then AP 4 would look in AQ:AQ22 and
say found the value in AQ19 and then return the value that appears in AR19.

So my question is . . . how would I write the formula?

That would be a VLOOKUP

So in AP, you would have..

=VLOOKUP(AD2,AQ2:AR22,2)

If the AQ values are in ascending order, it will return the closest value
that is less than or equal to your AD2 value.

If you do not want to show a valid result without an exact match, add ,0 to
the end of the vlookup above.

"LOC" wrote:

I have a range of numbers - effect sizes AD2:AD47. In another column AP, I
want write a formula that will look up the percentile conversion within the
same worksheet. The percentile conversion list the estimate effect size in
Column A - AQ2:AQ22 and then the percentile is in AR2:AR22. So in other
words, if the effect size is 0.4 in AD2 then AP 4 would look in AQ:AQ22 and
say found the value in AQ19 and then return the value that appears in AR19.

So my question is . . . how would I write the formula?

Thank you - I flipped my data to be ascending and it works like a charm. I
could not fill it as it changed the array but I was able to quickly fix the
array in the filled cells so it returned the correct data. WOW! I am
impressed.

Thanks again

"Sean Timmons" wrote:

That would be a VLOOKUP

So in AP, you would have..

=VLOOKUP(AD2,AQ2:AR22,2)

If the AQ values are in ascending order, it will return the closest value
that is less than or equal to your AD2 value.

If you do not want to show a valid result without an exact match, add ,0 to
the end of the vlookup above.

"LOC" wrote:

I have a range of numbers - effect sizes AD2:AD47. In another column AP, I
want write a formula that will look up the percentile conversion within the
same worksheet. The percentile conversion list the estimate effect size in
Column A - AQ2:AQ22 and then the percentile is in AR2:AR22. So in other
words, if the effect size is 0.4 in AD2 then AP 4 would look in AQ:AQ22 and
say found the value in AQ19 and then return the value that appears in AR19.

So my question is . . . how would I write the formula?

That's Excel for ya! :-) Happy it worked for you!

"LOC" wrote:

Thank you - I flipped my data to be ascending and it works like a charm. I
could not fill it as it changed the array but I was able to quickly fix the
array in the filled cells so it returned the correct data. WOW! I am
impressed.

Thanks again

"Sean Timmons" wrote:

That would be a VLOOKUP

So in AP, you would have..

=VLOOKUP(AD2,AQ2:AR22,2)

If the AQ values are in ascending order, it will return the closest value
that is less than or equal to your AD2 value.

If you do not want to show a valid result without an exact match, add ,0 to
the end of the vlookup above.

"LOC" wrote:

I have a range of numbers - effect sizes AD2:AD47. In another column AP, I
want write a formula that will look up the percentile conversion within the
same worksheet. The percentile conversion list the estimate effect size in
Column A - AQ2:AQ22 and then the percentile is in AR2:AR22. So in other
words, if the effect size is 0.4 in AD2 then AP 4 would look in AQ:AQ22 and
say found the value in AQ19 and then return the value that appears in AR19.

So my question is . . . how would I write the formula?