Ask Your Question

[Calc] Get address of value in range

asked 2018-11-13 20:49:00 +0100

Deever gravatar image

I have a range (A1:D9) of some (unambiguous) 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 CELL("address",INDEX(A1:D9,MATCH(F1,A1:D9,0),2)) but get the error 504 "Error in parameter list".

Any ideas?

edit retag flag offensive close merge delete


MATCH function accepts only single dimensional array as lookup parameter. This can be one row or one column. That is why you get Err:504. Are your A1:D9 values unique? Which result do you expect if some values are not unique?

SM_Riga gravatar imageSM_Riga ( 2018-11-13 21:10:42 +0100 )edit

And how can this be done with two-dimensional arrays? Yes, the values are unique.

Deever gravatar imageDeever ( 2018-11-14 00:55:51 +0100 )edit

1 Answer

Sort by » oldest newest most voted

answered 2018-11-14 22:17:14 +0100

updated 2018-11-14 22:18:11 +0100

Hi @Deever. Please find the Demo file attached. While MATCH function does not support 2D array as parameter, you may perform MATCH lookup separately in rows and columns and then use results to determine cell coordinates and reference them in INDEX function. This will work only if ALL values in the lookup range are unique, otherwise MATCH will return first matched row and first matched column, which may or may not be the correct cell reference.

edit flag offensive delete link more
Login/Signup to Answer

Question Tools

1 follower


Asked: 2018-11-13 20:49:00 +0100

Seen: 57 times

Last updated: Nov 14 '18