How can I reference a relative cell in Calc?

[etsnyman]

I have a list of names and a list of their ranks, calculated using various formulas, in a large spreadsheet in Calc.

For instance, my list is similar to this, just on a larger scale:
Apple 3
Pear 2
Banana 1
Orange 4

The names and ranks change according to what is filled in on other sheets.

I would now like to Automatically sort them on a new sheet which is already listed in order. I can easily find the rank and the row in which the correct name is found, using a calculation including match.

However, now next to the list of e.g. 1-30 in my first column, I would like to now add the name, which will be on the same row as its calculated rank as shown above.

How do I find the name relative to its rank and then have it put next to its corresponding number? I kind of want to do the opposite of ADDRESS.

[etsnyman]

Ok, I needed to use a combination of INDIRECT and ADDRESS. I know the column, so using =INDIRECT(ADDRESS(row,column)) I was able to do it. My sum gives me the row number and the column number is known.

[joshua4]

You might also look into using INDEX and MATCH–depending on the exact needs.

[keme1]

@etsnyman

Ok, I needed to use a combination of INDIRECT and ADDRESS.

That is a fairly common solution. Works fine but can be difficult to maintain at times. If it works for you, by all means keep it!

The usual solution for this is to use VLOOKUP(), but it requires that the key column be the leftmost column. The “expansion” of the lookup functions is to use INDEX() with MATCH() inside. May be faster and more robust if you do this a lot (if your spreadsheet file is REALLY large).

edit: I see @joshua4 beat me to the INDEX(…MATCH(…)…) suggestion. I second that, then