Ask Your Question
0

leaderboard in calc using =(H)LOOKUP() [closed]

asked 2016-02-03 10:21:07 +0100

XLgaming_inc gravatar image

Hello,

I'm trying to make a leaderboard in LibreOffice calc, but i've encountered a problem. The formula I use to check in which place someone is is the following:

=LOOKUP(LARGE(AA71:AD71;1);AA71:AD71;AA62:AD62)&": "&LARGE(AA71:AD71;1)

My data is stored in numbers in AA63:AD78 and above each a column is the name of the person. What I wanted to do was to make a separate leaderboard for each row and first put the name of each person and then their score. The formula mentioned above works fine for everyone on the first place, but the second place always returns a #N/A error.

So I looked online and found that using HLOOKUP should work better. So I tried that with the following formula:

=HLOOKUP(LARGE(AA70:AD70;1);AA62:AD81;1;0)&": "&LARGE(AA71:AD71;1)

But that will always output #N/A, whatever I try...

Any ideas?

-Xander

edit retag flag offensive reopen merge delete

Closed for the following reason the question is answered, right answer was accepted by XLgaming_inc
close date 2016-02-04 08:57:15.229405

Comments

Please could you share a minimal sample file?

m.a.riosv gravatar imagem.a.riosv ( 2016-02-04 01:46:07 +0100 )edit

while i was preparing a file i accidentally found the solution to my problem, sorry for bothering

XLgaming_inc gravatar imageXLgaming_inc ( 2016-02-04 08:56:57 +0100 )edit

Please share the solution or what was happening, can help others, and this is the main purpose of this site.

m.a.riosv gravatar imagem.a.riosv ( 2016-02-04 23:04:31 +0100 )edit

Sure, I edited my previous answer to include it.

XLgaming_inc gravatar imageXLgaming_inc ( 2016-02-10 17:31:35 +0100 )edit

1 Answer

Sort by » oldest newest most voted
0

answered 2016-02-03 10:19:50 +0100

XLgaming_inc gravatar image

updated 2016-02-10 17:43:44 +0100

Found the answer already:

=HLOOKUP(LARGE(AA70:AD70;1);AA70:AD71;1;0)

does the trick as long as the names are directly beneath the score.

Is there any way to get the names from anywhere in stead of directly beneath the row with scores?

---------------------------------------EDIT---------------------------------------

I found out that with the third parameter you can select of which row in the range selected in the second parameter you want to take the output.

So in my case, I put the names of the persons on row 81 and their scores on the rows 63 to 80. Then I used HLOOKUP to find out which value on the same row was the highest with =LARGE(AA70:AD70;1) and used this value to find the right column on the first row of the area selected in the second parameter. In my case, this was the range AA70:AD81, and the first row in that area is AA70:AD70. Once the column has been found, it will look in the row selected by the third parameter in the area selected by the second parameter. In my case, this area was AA70:AD82, and it selected the row was 12, so it took the item on row 81 in the same column as the number selected by the =Large() command. The fourth parameter was just to tell the command that the numbers were not sorted.

Final command (in my case): =HLOOKUP(LARGE(AA70:AD70;1);AA70:AD81;12;0)

(BTW: can someone accept this answer? Cause I can't accept my own answers because my karma is to low :( )

edit flag offensive delete link more

Comments

=HLOOKUP(LARGE(AA70:AD70;1);AA70:AD71;1;0)
cannot return a name. The second last paramter indicates the same row LARGE is working on and HLOOKUP will therefore return exactly the value the LARGE expression is passing to it. The index parameter of HLOOKUP must be 2 instead of 1 to do how expected.

Lupp gravatar imageLupp ( 2016-02-10 21:19:46 +0100 )edit

Question Tools

1 follower

Stats

Asked: 2016-02-02 21:53:22 +0100

Seen: 288 times

Last updated: Feb 10 '16