Thor se pregunta si hay una manera de realizar una búsqueda sin tener que especificar una columna o fila y que el resultado sea la dirección de la celda en la que se encuentra el valor. Por ejemplo, quiere buscar un valor (como 345 o «mi texto») y hacer que la función busque en todas las celdas de todas las hojas de trabajo del libro y devuelva la dirección completa de la celda en la que se encontró el valor.

El enfoque que utilice dependerá del rango que desee buscar.

Si desea buscar en la misma hoja de trabajo en la que desea que se muestre la respuesta, puede usar una fórmula, como la siguiente:

=ADDRESS(MAX(ROW(1:5)(A1:E5="my text")), MAX(COLUMN(A1:E1)(A1:E5="my text")),4)

Esto debe ingresarse como una fórmula de matriz (presione Ctrl + Shift + Enter), y solo busca en el rango A1: E5. Si lo desea, puede cambiar el rango ajustando la fórmula de manera apropiada.

Un área de búsqueda más grande sería mirar una hoja de trabajo completa. Esto todavía se puede hacer usando una fórmula de matriz, como la siguiente:

=ADDRESS(MAX(ROW(Sheet1!1:65000)(IF(Sheet1!1:65000=$A$1,1,0))), MAX(COLUMN(Sheet1!$1:$65000)IF(Sheet1!1:65000=$A$1,1,0)))

La fórmula asume que lo que está buscando se almacena en la celda A1.

Debe cambiar la designación de Hoja1 por el nombre de la hoja de trabajo que desee buscar.

Si desea buscar en un rango más amplio, como todas las hojas de trabajo en un libro de trabajo, la mejor solución es usar una macro que invoque la función Buscar dentro de Excel.

Function FindAddr(vValue As Variant)

Dim wks As Worksheet     Dim rCell As Range     Dim bFound As Boolean

bFound = False     For Each wks In ActiveWorkbook.Worksheets         With wks             Set rCell = .Cells.Find _               (What:=vValue, After:=.Cells(1), _               LookIn:=xlValues, LookAt:=xlWhole, _               SearchOrder:=xlByRows, _               SearchDirection:=xlNext, _               MatchCase:=False)

If Not rCell Is Nothing Then                 bFound = True                 Exit For             End If         End With     Next     If bFound Then         FindAddr = wks.Name & "!" & _           rCell.Address(False, False)

Else         FindAddr = "Not Found"

End If     Set wks = Nothing     Set rCell = Nothing End Function

Esta función está diseñada para ser llamada desde otra macro, que le pasa lo que se deba buscar en el parámetro vValue. La función devuelve la dirección completa (incluido el nombre de la hoja de trabajo) de la primera coincidencia o devuelve «No encontrado» si no hubo ninguna coincidencia.

_Nota: _

Si desea saber cómo usar las macros descritas en esta página (o en cualquier otra página de los sitios ExcelTips), he preparado una página especial que incluye información útil.

link: / excelribbon-ExcelTipsMacros [Haga clic aquí para abrir esa página especial en una nueva pestaña del navegador].

ExcelTips es su fuente de formación rentable en Microsoft Excel.

Este consejo (11524) se aplica a Microsoft Excel 2007, 2010, 2013 y 2016.

Puede encontrar una versión de este consejo para la interfaz de menú anterior de Excel aquí:

link: / excel-Searching_for_a_Value_Using_a_Function [Búsqueda de un valor mediante una función].