leaderboard in calc using =(H)LOOKUP()

[XLgaming_inc]

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

[mariosv]

Please could you share a minimal sample file?

[XLgaming_inc]

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

[mariosv]

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

[XLgaming_inc]

Sure, I edited my previous answer to include it.

[XLgaming_inc]

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 )

[Lupp]

=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.