[Solved] Get address of value in range

[Deever]

I have a range (A1:D9) of some (unique) integer values.
What I'd need is the address of the cell of this range containing the value in cell F1 to be displayed in F2.
I'm trying

Code: Select all

CELL("address",INDEX(A1:D9,MATCH(F1,A1:D9,0),2))

but get the error 504 "Error in parameter list".

Any ideas?

[FJCC]

Match only works with a single row or column. I would use helper cells as in the attached file.

[MrProgrammer]

Hi, and welcome to the forum.

I have a range (A1:D9) of some (unique) integer values. What I'd need is the address of the cell of this range containing the value in cell F1 to be displayed in F2.

=ADDRESS(SUMPRODUCT(ROW(A1:D9)*SIGN(COLUMN(A1:D9));A1:D9=F1);
         SUMPRODUCT(SIGN(ROW(A1:D9))*COLUMN(A1:D9);A1:D9=F1);4)

Instead of displaying the address you could use conditional formatting to highlight the matching cell in A1:D9. Read about conditional formatting in Help → Index or in User Guides (PDF) or searching for topics about it in the Calc Forum.

If this solved your problem please go to your first post use the Edit button and add [Solved] to the start of the title. You can select the green checkmark icon at the same time.

[Tutorial] Ten concepts that every Calc user should know

[Lupp]

(Only for readers interested in relyable results also if the assurance of "no dubs" is omitted:)

In LibO 5.3.3 and higher you can use

Code: Select all

{=TEXTJOIN(";";1;IF(A1:D9=F1;ADDRESS(ROW(A1:D9);COLUMN(A1:D9);4);""))}

to get the semicolon-separated sequence of all the addresses of cells containing the value to match - and an empty result string if no match was found.
(Don't enter the curly brackets. They only indicate that the formula was entered for array-evaluation.
Usage of TEXTJOIN will break interoperability with AOO and many versions of Excel, however.)