Lookup using charts

QuestionEdit

Sixthsense,

I have 10 charts and inside these charts there are a number of ranked cells. In addition, I have a cell that is a drop down list of all the chart names. That cell for example purposes is F2. If I choose chart 11ga in the F2 cell I need to lookup the number 1 ranked cell in that chart. These charts may show red ranked 2, blue ranked 4 and yellow ranked 1. How can I make cell A1 display the value for the number one rank of the chart selected in F2. I want A1 to show yellow. The ranking will change often so yellow might not be ranked one the next time I use the sheet. It will need to change when the ranking does. I hope you understand my explanation and appreciate all the help you've done for me recently. Travis

AnswerEdit