I need a formula that finds a cell with a given value, range is two dimensional

Question

So the formula that I need is basically the Find feature from the ribbon, but in an excel formula.

Somewhere on the page is a value. I need to be able to search the page and find out what row and column that value is in.

All of the solutions I've seen work from the assumption that I will be able to already know what row or column it is in and find it, searching a one dimensional Range. But now I've got a 2D Range, and all those formulas are returning #N/A.

I will only know two pieces of data going in.

1. the cell value will be on the sheet, once and only once
2. it will be somewhere in the range A1:ZZ3500

Some of these sheets are enormous, and using the find function each time is giving me carpal tunnel.

Answer 1

Use AGGREGATE:

ROW:

=AGGREGATE(15,7,ROW(A1:ZZ3500)/(A1:ZZ3500="value"),1)

COLUMN

=AGGREGATE(15,7,COLUMN(A1:ZZ3500)/(A1:ZZ3500="value"),1)

Answer 2

You could roll your own UDF. In a new VBA module:

Function findValue(searchTerm As String, searchRange As Range) As String
    findValue = searchRange.Find(searchTerm).Address
End Function

Which can be used in a worksheet formula like =findValue("yourvalue", A1:ZZ3500)

That will spit out the cell reference like $B$2 which you can then use =Indirect() to deal with, if need be.