I think it is a lookup question???

L

LOC

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?
 
S

Sean Timmons

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.
 
L

LOC

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
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top