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

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:

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:

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

Any ideas?

-Xander

edit retag 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

Please could you share a minimal sample file?

( 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

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

( 2016-02-04 23:04:31 +0100 )edit

Sure, I edited my previous answer to include it.

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

Sort by » oldest newest most voted

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.

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

more