Finding location of value from multiple columns

Question

I have a 4x4 table. All the values in the table are unique.

    A   B   C   D
1   a   b   c   d
2   e   f   g   h
3   i   j   k   l
4   m   n   o   p

How do i find the address of a particular value? I've tried =CELL("address",MATCH(A1,A1:D4,0)) to find a but it returns an error value because =MATCH(A1,A1:D4,0) is an error value.

It seems =match only works on a single row/column.

Any solutions to this?

Answer 1

Here's another way to do it (without an array formula, i.e. without hitting cntrl-enter), based on this solution to locate a value in a 2D array.

I won't repeat the excellent explanation of how it works it, in order to properly credit the author on his website.

EDIT: Based on @Chronocidal's excellent suggestions, here is the new and improved version for reference: