LOOKUP + LARGE prob???

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

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 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 Bryan,

Sorry, don't understand that at all :s I've attached the workbook, ignore the plainess, I'm just practicing at the mo

Thanks alot
Colette

How do you want to separate contestants if they are tied? Assuming it's simply on alphabetical order the this is the approach I'd use.....

Sort your round 1 worksheet by alphabetical order on column A (I'm assuming you'd have actual names in there)

Add a column e.g. column G with a unique rank. To generate this use this formula in G2 copied down to G65

=RANK(F2,F$2:F$65)+COUNTIF(F$2:F2,F2)-1

Then in round2 just put numbers, 1, 2, 3, etc. in A3 down then in B3 use this formula copied down

=INDEX(round1!A$2:A$65,MATCH(A3,round1!G$2:G$65,0))

Hiya Daddylonglegs,

You're a hero, that works a treat ! Thanx very, very much.

This is probably me being a pain but if you could explain what those functions actually mean and what I've just done (in regards to the other column I put in) then I would've learnt something.

Again thanx alot, really do appreciate it.

Colette

Hi All,

Just a quickie I hope, is there anyway of preventing duplicates? What I have is a sheet where out of 3 scores only the highest is used, what I need to know is is there a way of separating in the event of a tie, say for example by taking the 2nd score into account?

Thanx for any help
p.s i've attached the workbook

Colette

One way would be to replace the formula

=MAX(B3:D3) in round1!F3 with

=MAX(B3:D3)+LARGE(B3:D3,2)/100+ LARGE(B3:D3,3)/10000

You can still display as an integer, if you want, but the underlying value (which you can see if you format the cell to 0.0000) will be "weighted" to take into account the second and third lowest scores.

For example "Terry" will be ranked above "william" because scores of 5, 5 and 2 evaluate to 5.0502 but 5,5 and 1 becomes 5.0501

Hiya daddylonglegs,

Thanx alot for the quick reply, done that and it works great.

Thanx muchly
Colette

Hi all,

Dunno if you can help, thought i now had this cracked but have discovered that when I enter real scores into round2 it changes the order of the names in colimn b, it shouldn't do this because these are a result of round1, nothing to do with scores gained in round2, help pleeaaase

Thanx in advance for any help
Colette


p.s have attached the workbook

Hello again colette,

What you describe is happening because your scores in round 1 are generated by RANDBETWEEN function so when you put scores in round 2 the workbook is re-calculated and all the random scores are re-generated. If scores in round 1 are fixed then this won't happen.

Just to make sure that the formulas I already suggested will work OK.....what sort of ranges will the real scores be? Will they be integers or could they be 6.32 for instance?

I notice you get #NUM! error before scores are entered in G3 down, you might want to prevent this by making the formula

=IF(COUNT(C3:E3)=3,MAX(C3:E3)+LARGE(C3:E3,2)/100+ LARGE(C3:E3,3)/10000,"")

Hi daddylonglegs,

Thanx for that, should've thought of that, only used the randbetween cos was being lazy and couldn't be bothered to think of loads of numbers to test it with :s

The scores are integers btw

Thanx for all ya help
Colette

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.