Get a cell reference from a LARGE() function

[gtomorrow]

Hello, all!

Sorry, I’m sure this question has been asked before but after many searches (here and elsewhere) I can’t seem to find an answer.

I have a spreadsheet with numeric data from A3:J503. In another cell (N1) I have a LARGE() function. What I would like to do is have P1 return a cell reference to the LARGE function’s results. I’ve tried various incantations of LOOKUP, INDEX, ADDRESS and INDIRECT but all of these functions work only on either row or column data or otherwise already knowing where the cell reference is.

Bonus question: is it possible to get the cell reference from the cell with the LARGE() formula (N1) as opposed to re-entering the array of A3:J503?

Again, sorry about the possibly redundant question. Thanks in advance.

EDIT 2018-05-20: It seems there’s a bit of confusion as to what I’d like to achieve. It’s actually very simple. Attached is a test file. Thanks to all.

test.ods

[Lupp]

It’s not an exactly common question - and it’s not clear enough. Please edit it to be more precise and concise.

1. No clear relevance of coluns B through J. Forget them?
2. A2:A41 is good enough for an example.
3. A formula using LARGE() in the ordinary way returns exactly one result. Why did you use the plural?
4. “A cell reference to … results” (Plural again!) does not exist. You may get a reference to one cell containing one result.

[Lupp]

'5. To handle multiple matches is a rather complicated task and not well supported by standard functions.
…

[Xoristzatziki1]

LARGE returns a result, NOT the cell (or the cells) that may hold that result. Your question probably is not LARGE related, but how to find which cells contain a specific value.

[Xoristzatziki1]

After you provided an .ods example was made clearer that you are trying to extract a “random” single cell reference from a number of cells that may have the same value. a.k.a. how to find in a range of cells first(?) cell that contains a specific value.

[Lupp]

-1- It may be clear what the first occurrence of a values is for a one-row-range (left to right?) or a one-column-range (t-d).
It’s not clear for a 2D range. It’s not even exactly easy to disambiguate the term in words.
-2- I did not select a random occurrence in my example, but the first one reading each column top down starting with the leftmost column and then shifting to the next column to the right. This is the order TEXTJOIN() applies when converting the array to a sequence.

[Xoristzatziki1]

@Lupp To make clear, my comments are for the question and what gtomorrow is trying to achieve, not about any solution or comment. From the provided C:\fakepath\test.ods is very clear that tries to get a cell that contains the LARGE value probably assuming that LARGE returns also a single cell reference which is not true.

[Lupp]

This is the order TEXTJOIN() applies
Actually the order is created by the array-evaluation. TEXTJOIN() itself would do it the other way.
I didn’t understand this clearly yet.

[Lupp]

This askbot will drive me crazy one day.

[Lupp]

This askbot will drive me crazy one day.

[Lupp]

A guess: Have a glance into this example.

Edit1 regarding the example provided by the OriginalQuestioner with his amendment:
There are still a few facts the OQ seems to ignore:
(1. LARGE() with second parameter 1 simply returns the same result as MAX().)
2. There is no assurance that the value found only occurs once in the range.
== The question itself contains a plural insofar.
3. To find the position(s) of a value in a 2D-range there are no dedicated standard functions.

Workarounds using complicated formulae to find a first (in a specific sense) occurence are available. More advisable should be a solution using a helper showing all the matches. From the helper result you may then extract one or more specific matches meeting your needs. See this new example. Essentially it concerns the matching of any value in a two-dimensional range. Where the value came from is irrelevant.
Using the TEXTJOIN() function the suggested solution will only work in LibO versions 4.3 or higher (and not in any AQOO).

Edit1:
Since I was accused to have posted a second solution, I should hand the first one in later. See this new attachment.
I also made a study out of principal interest 2.5 years ago. I do no longer remember the details, but you may search it for contents of interest for you.
By far the simplest (and also most stable) solution should be the one based on TEXTJOIN(). It will not work, however in old versions of LibO, and also not in AOO (Apache OpenOffice) of any version because of lacking or buggy TEXTJOIN().

[gtomorrow]

@Lupp,

1. LARGE(),1 does in fact return the same as MAX(). “Po-tay-to, po-tah-to”.
   and
2. Thank you. You’ve confirmed my suspicions. I suspect it would probably be easier to use a column instead of an array.

That said, your 2nd solution, while correct (and brilliant!), is too fragile. It returns an #NAME? error in V.5.1.6.2 Linux (en-US), yet works perfectly in V.6.0.4 macOS (it-IT). In any case, thanks for your time.

[Lupp]

I don’t feel clear about what my second solution was. If you are talking of the formula based on the result by TEXTJOIN(): This function was first implemented for LibO with V5.2 and was buggy in the beginning. I wouldn’t use it with versions below V5.4. (I made a less efficient but more powerful variant of it in BASIC for my own use.)