# Chart Building With Ranked Data

• I need to build some charts using data that is being collected using the RANK function. My issue is that LOOKUP & VLOOKUP will not work because the data is not in ascending order. The attached image shows the data I am using. What I need to generate is the top ten, in ascending order on a seperate worksheet within the workbook. That data would then be used to generate the charts. Columns C & D is the first set of data. What I need is a formula that looks at column D, finds the number 1, & return the corresponding value from Column B, and so on to number 10. Same for columns E & F, and G & H, all returning the corresponding value from Column B. The reason the data is not ascending - first, because it is calculated YTD, it can change month to month & second, I can't sort ascending because it is three different sets of values for the same Column B. Any help or tips would be greatly appreciated. Thanks.

## Images

• Re: Chart building with ranked data

Quote from Nashkat

What I need to generate is the top ten, in ascending order on a seperate worksheet within the workbook. That data would then be used to generate the charts. Columns C & D is the first set of data. What I need is a formula that looks at column D, finds the number 1, & return the corresponding value from Column B, and so on to number 10. Same for columns E & F, and G & H, all returning the corresponding value from Column B. The reason the data is not ascending - first, because it is calculated YTD, it can change month to month & second, I can't sort ascending because it is three different sets of values for the same Column B. Any help or tips would be greatly appreciated. Thanks.

Look at match and offset , something like
=OFFSET(B2,MATCH(5,D3:D12,0),0)
HTH

• Re: Chart building with ranked data

I tried the code in your reply & in the function box, it keeps telling me that it is Volatile & returns an error.

EDIT - I got the OFFSET & MATCH functions to work. Thanks again.

## Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!