LOOKUP + LARGE prob???

[colette]

Hi all,

I'm using the LOOKUP function to find the largest number and then return the name from a different column, it looks like this:

=LOOKUP(LARGE(round1!$F$2:$F$65,1),round1!$A$2:$A$65)

but all I get is hashN/A?

Does anyone know whether I can do this and if so what I'm doing wrong?

Help Pleeaaase

Many thanx
Colette

[Carim]

Hi,

You seem to missing an argument ...in lookup() function

=LOOKUP(LARGE(round1!$F$2:$F$65,1),round1!$F$2:$F$65,round1!$A$2:$A$ 65)


What is located in column A ... ?

[colette]

Hi carim,

Thanx for that, it's solved my N/A probs, but now it keeps repeating the same answer, even though I am incrementing the number in the LARGE function, is that because I also need an IF in there?

Just to try and explain I'm trying to do a work sheet that has a list of contestants, their scores from 3 judges and a column with their best score. Then on the 2nd sheet I am trying to call the top scores using the already mentioned function??

Hoope that's clearer

Thanx again for any help
Colette

[Bryan Hessey]

Hi carim,

Thanx for that, it's solved my N/A probs, but now it keeps repeating the same answer, even though I am incrementing the number in the LARGE function, is that because I also need an IF in there?

Just to try and explain I'm trying to do a work sheet that has a list of contestants, their scores from 3 judges and a column with their best score. Then on the 2nd sheet I am trying to call the top scores using the already mentioned function??

Hoope that's clearer

Thanx again for any help
Colette

Hi,

the formula

=LOOKUP(LARGE(round1!$F$2:$F$65,1),round1!$F$2:$F$ 65,round1!$A$2:$A$ 65)

could be

=LOOKUP(LARGE(round1!$F$2:$F$65,ROW()-1),round1!$F$2:$F$65,round1!$A$2:$A$65)

to auto-increment the 'Large' for you as you progress down the rows, however, if column F is not in sorted order you may not produce the result that you require.

hth
---

[colette]

Hi Bryan,

Is there any way to stop repetition? For example in the results there may be a number of people with the same high score (because half of the 1st round go through to next etc), with the formula as is it repeats the same contestant number, rather than showing the different contestant numbers, whether or not their score is the same?

Thanx alot for ya help
Colette

[Bryan Hessey]

Hi Bryan,

Is there any way to stop repetition? For example in the results there may be a number of people with the same high score (because half of the 1st round go through to next etc), with the formula as is it repeats the same contestant number, rather than showing the different contestant numbers, whether or not their score is the same?

Thanx alot for ya help
Colette

Hi,
one way is to set the Points so that they appear equal, but are not.

Set the column to Number, no decimal places.

set the Points (if you type the points in, then set another hidden column for the Ranking test) to = Points + (Row()/100000)
The Ranked list will then appear in reverse chronological sequence for equal rakings.

set the Points to = Points + (0.001-(Row()/100000) to give chronological sequence for equal rank

(Note, check Tools, Options, Calculate, Precision as Displayed is NOT ticked.

hth
---

[McG_84]

Hi everyone. I have a similar need to the problem Colette is having (yes I am aware this thread is 7 years old ). In her spreadsheet she is just finding values from one column (F in this case). I was wondering how this exact thing can be done but using the range from B2:D65.