Challenging question

[NeMeSisT]

Consider the following first:

- Column A has a list of names
- Column B,C,D has vlookup formulas that references informatio
relating to each individual name on different sheets.
- Column E gives the average value for D+B and displays a numerica
value with 2 decimal places.

My objective is to create a formula that determine who in column E ha
the highest value then return their name in column A. Then in the nex
cell I need the name for 2nd highest, next cell 3rd highest and so o
until 5th highest value.

Here are some data to help the experts get started.

Sheet1
Column A Column B Column C Column D Column E
John Smith 152 30 56
=average(B1+D1)


Sheet2
Column A Column B
Return(1st) =vlookup(A1, Sheet1!A1:E10,5,FALSE)
Return(2nd) =vlookup(A2, Sheet1!A1:E10,5,FALSE)

etc.

A big thank you in advance for someone to crack that formula.

Dan

 

[aaron.kempf]

screw vlookup; store your DATA in A DATABASE and then you can do
subqueries-- they are a lot more powerful

 

[Dave Peterson]

First, are you sure your average formula is ok?

I would have guessed that =average(b1,d1) would be what you want.

==
But this formula should return the name with the biggest average.

=INDEX(Sheet1!A1:A25,MATCH(LARGE(Sheet1!E1:E25,1),Sheet1!E1:E25,0))

If you copy the formula, but change the # (in the large function) to 2, then 3,
then 4, then 5, you'll get your list.

=INDEX(Sheet1!A1:A25,MATCH(LARGE(Sheet1!E1:E25,#),Sheet1!E1:E25,0))


And adjust your range to suit.

 

[NeMeSisT]

Thanks Dave, the formula you provided worked perfectly.

Regards,

Dan.