Ask Your Question
0

[Calc] Get address of value in range

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

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

Comments

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 +0200 )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 +0200 )edit

1 Answer

Sort by » oldest newest most voted
0

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

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

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

Stats

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

Seen: 123 times

Last updated: Nov 14 '18