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?
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?
And how can this be done with two-dimensional arrays? Yes, the values are unique.
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.