¿Cómo buscar rápidamente un valor en varias hojas de cálculo o libros de trabajo?

¿Alguna vez has necesitado encontrar un valor específico que podría aparecer en diferentes hojas de cálculo o incluso en varios libros de trabajo en Excel? Es una situación habitual, especialmente cuando trabajas con proyectos grandes, informes mensuales o cuando necesitas consolidar información gestionada en varios archivos. Revisar manualmente cada hoja o archivo no solo lleva mucho tiempo, sino que también puede dar lugar a errores. En este tutorial descubrirás varios métodos eficaces para buscar datos de manera eficiente, ya sea dentro de un solo libro de trabajo, en varios libros, o si necesitas una solución integral basada en fórmulas o datos consolidados. Estas alternativas cubren las necesidades prácticas que puedes encontrar en tus tareas cotidianas con Excel o en proyectos de análisis de datos.

Buscar un valor en varias hojas de un libro de trabajo con la función Buscar y Reemplazar

Buscar un valor en todos los libros de trabajo de una carpeta con VBA

Busca rápidamente un valor en varios libros de trabajo abiertos con Kutools para Excel

Buscar un valor en varias hojas de cálculo utilizando fórmulas de Excel

Buscar un valor en varias hojas de un libro de trabajo con la función Buscar y Reemplazar

La función Buscar y Reemplazar de Excel es una herramienta básica pero eficaz para localizar rápidamente valores específicos en varias hojas de cálculo dentro del mismo libro de trabajo. Esta opción es especialmente útil si sabes qué hojas deseas incluir en la búsqueda o si tus datos están bien estructurados en un solo archivo. No permite buscar en otros archivos ni en libros de trabajo cerrados, pero ofrece una forma sencilla y rápida de buscar información.

1. Para comenzar, selecciona las pestañas de hoja que quieras incluir en la búsqueda manteniendo presionada la tecla Ctrl y haciendo clic en cada hoja en la barra de Pestañas de hoja de cálculo individualmente. Así, la búsqueda se realizará simultáneamente en todas las hojas seleccionadas. Consulta la captura de pantalla:

2. Una vez seleccionadas las hojas deseadas, presiona Ctrl + F para abrir el cuadro de diálogo Buscar y Reemplazar. Escribe el valor que deseas encontrar en la caja de texto Buscar lo siguiente bajo la pestaña Buscar y luego haz clic en el botón Buscar Todo. Excel mostrará de inmediato una lista de todas las celdas en las hojas seleccionadas que contienen tu valor de búsqueda, junto con su ubicación. Consulta la captura de pantalla:

Consejo: La herramienta Buscar y Reemplazar solo busca en las hojas seleccionadas que están abiertas y visibles. Si deseas extender la búsqueda a más hojas, asegúrate de seleccionarlas como se ha indicado arriba. Si accidentalmente olvidas incluir una hoja, tan solo repite la operación tras seleccionarla.

Precauciones: Este método no busca en libros de trabajo cerrados u ocultos y no resalta automáticamente las celdas; solo listará los resultados para facilitar la navegación.

Solución de problemas: Si no obtienes los resultados esperados, revisa la selección de hojas y asegúrate de que no tengas filtros o protecciones de celdas que puedan ocultar o limitar la búsqueda.

Busque y reemplace valores sin esfuerzo en varias hojas y libros de trabajo

La función Buscar y Reemplazar avanzada de Kutools para Excel ofrece una manera eficiente de buscar y reemplazar valores en múltiples hojas o incluso en todos los libros de trabajo abiertos. Con esta característica avanzada, puede ahorrar tiempo y eliminar errores al trabajar con grandes conjuntos de datos, haciendo que sus tareas en Excel sean más rápidas y precisas.

Kutools para Excel - Potencia Excel con más de 300 herramientas esenciales. ¡Disfruta de funciones de IA gratis permanentemente! Consíguelo ahora

Buscar un valor en todos los libros de trabajo de una carpeta con VBA

Si necesitas buscar un valor específico en varios libros de trabajo guardados en una carpeta —incluidos los archivos que no tienes abiertos—, las herramientas integradas de Excel no permiten hacerlo directamente. En este caso, una macro VBA (Visual Basic for Applications) puede automatizar el proceso, abriendo sistemáticamente cada libro de trabajo en la carpeta, revisando todas las hojas de cálculo y registrando dónde se encuentran coincidencias. Este método resulta especialmente útil para auditorías periódicas o para comprobar valores en archivos archivados o por lotes.

1. Empieza abriendo un libro de trabajo nuevo (o en blanco) en Excel. Selecciona la celda donde deseas que aparezcan los resultados de la búsqueda (normalmente la celda A1). Pulsa Alt + F11 para abrir la ventana del editor de Microsoft Visual Basic for Applications.

2. Dentro del editor de VBA, ve a Insertar > Módulo para crear un nuevo módulo y después pega el siguiente código VBA en la ventana del módulo.

VBA: Buscar un valor en todos los libros de trabajo de una carpeta.

Sub SearchFolders()
'UpdatebyKutoolsforExcel20200913
    Dim xFso As Object
    Dim xFld As Object
    Dim xStrSearch As String
    Dim xStrPath As String
    Dim xStrFile As String
    Dim xOut As Worksheet
    Dim xWb As Workbook
    Dim xWk As Worksheet
    Dim xRow As Long
    Dim xFound As Range
    Dim xStrAddress As String
    Dim xFileDialog As FileDialog
    Dim xUpdate As Boolean
    Dim xCount As Long
    Dim xAWB As Workbook
    Dim xAWBStrPath As String
    Dim xBol As Boolean
    Set xAWB = ActiveWorkbook
    xAWBStrPath = xAWB.Path & "\" & xAWB.Name
    On Error GoTo ErrHandler
    Set xFileDialog = Application.FileDialog(msoFileDialogFolderPicker)
    xFileDialog.AllowMultiSelect = False
    xFileDialog.Title = "Select a forlder"
    If xFileDialog.Show = -1 Then
        xStrPath = xFileDialog.SelectedItems(1)
    End If
    If xStrPath = "" Then Exit Sub
    xStrSearch = "KTE"
    xUpdate = Application.ScreenUpdating
    Application.ScreenUpdating = False
    Set xOut = Worksheets.Add
    xRow = 1
    With xOut
        .Cells(xRow, 1) = "Workbook"
        .Cells(xRow, 2) = "Worksheet"
        .Cells(xRow, 3) = "Cell"
        .Cells(xRow, 4) = "Text in Cell"
        Set xFso = CreateObject("Scripting.FileSystemObject")
        Set xFld = xFso.GetFolder(xStrPath)
        xStrFile = Dir(xStrPath & "\*.xls*")
        Do While xStrFile <> ""
            xBol = False
            If (xStrPath & "\" & xStrFile) = xAWBStrPath Then
                xBol = True
                Set xWb = xAWB
            Else
                Set xWb = Workbooks.Open(Filename:=xStrPath & "\" & xStrFile, UpdateLinks:=0, ReadOnly:=True, AddToMRU:=False)
            End If
            For Each xWk In xWb.Worksheets
                If xBol And (xWk.Name = .Name) Then
                Else
                Set xFound = xWk.UsedRange.Find(xStrSearch)
                If Not xFound Is Nothing Then
                    xStrAddress = xFound.Address
                End If
                Do
                    If xFound Is Nothing Then
                        Exit Do
                    Else
                        xCount = xCount + 1
                        xRow = xRow + 1
                        .Cells(xRow, 1) = xWb.Name
                        .Cells(xRow, 2) = xWk.Name
                        .Cells(xRow, 3) = xFound.Address
                        .Cells(xRow, 4) = xFound.Value
                    End If
                    Set xFound = xWk.Cells.FindNext(After:=xFound)
                Loop While xStrAddress <> xFound.Address
                End If
            Next
            If Not xBol Then
            xWb.Close (False)
            End If
            xStrFile = Dir
        Loop
        .Columns("A:D").EntireColumn.AutoFit
    End With
    MsgBox xCount & " cells have been found", , "Kutools for Excel"
ExitHandler:
    Set xOut = Nothing
    Set xWk = Nothing
    Set xWb = Nothing
    Set xFld = Nothing
    Set xFso = Nothing
    Application.ScreenUpdating = xUpdate
    Exit Sub
ErrHandler:
    MsgBox Err.Description, vbExclamation
    Resume ExitHandler
End Sub

3. Pulsa la tecla F5 o haz clic en el botón Ejecutar para ejecutar esta macro. Aparecerá una ventana de diálogo Seleccionar una carpeta, donde podrás elegir la carpeta que contiene los libros que deseas buscar. Consulta la captura de pantalla:

4. Haz clic en OK. Tras finalizar la búsqueda, un cuadro de mensaje te informará del número total de celdas encontradas que contienen el valor especificado. Consulta la captura de pantalla:

5. Haz clic en OK para cerrar el mensaje. Todas las ubicaciones donde se haya encontrado el valor se listarán en una hoja de cálculo nueva, incluyendo el nombre del libro, el nombre de la hoja, la referencia de la celda y el contenido exacto para su revisión.

Consejo: El término que se busca actualmente en la macro VBA es “KTE”. Puedes personalizarlo cambiando la línea xStrSearch = "KTE" en el código por cualquier valor que desees buscar.

Precauciones: Antes de ejecutar, asegúrate de que todos los libros de trabajo relevantes estén guardados y cerrados (excepto aquel donde vayas a ejecutar la macro, que puede estar abierto). Las carpetas con muchos archivos de Excel o muy complejos pueden tardar un poco en procesarse. No interrumpas la ejecución de la macro.

Solución de problemas: Si aparecen errores, comprueba que todos los archivos sean verdaderos libros de trabajo de Excel (que no estén dañados o protegidos por contraseña) y que tu configuración de seguridad de macros permita la ejecución de código. Si la macro no finaliza, intenta ejecutarla en un conjunto de archivos más reducido.

Busca rápidamente un valor en varios libros de trabajo abiertos con Kutools para Excel

Cuando necesitas buscar entre varios libros de trabajo que ya están abiertos en tu sesión de Excel, Kutools para Excel ofrece un panel específico de Buscar y Reemplazar que facilita y organiza mucho más el proceso. Esto es especialmente útil para revisar rápidamente tus libros de trabajo sin necesidad de usar scripts ni configuraciones complicadas, ideal para quienes gestionan múltiples archivos a la vez y buscan una herramienta sencilla e intuitiva para gestionar búsquedas.

1. En cualquiera de los libros de trabajo abiertos, ve a la Kutools pestaña y selecciona Navegación. Luego haz clic en la Buscar y Reemplazar botón para que aparezca el Buscar y Reemplazar panel, que normalmente se sitúa en la parte izquierda de tu ventana de Excel. Consulta la captura de pantalla:

2. En la Buscar pestaña, escribe el valor que deseas buscar en el cuadro de texto Buscar lo siguiente. Elige Todos los Libros de Trabajo en el menú desplegable Dentro de para realizar la búsqueda en todos los libros de trabajo abiertos. Luego haz clic en Buscar Todo para mostrar de inmediato una lista de todas las celdas coincidentes junto con su ubicación. Consulta la captura de pantalla:

Consejo: La herramienta avanzada de Buscar y Reemplazar de Kutools para Excel te permite buscar y reemplazar datos no solo en todos los libros de trabajo abiertos, sino también específicamente en hojas seleccionadas, el libro de trabajo actual, la hoja de trabajo actual o incluso en tu selección activa. Esto te da mayor control según tus necesidades.

Precauciones: Asegúrate de que todos los libros de trabajo que deseas buscar estén abiertos antes de iniciar la búsqueda, ya que esta herramienta no puede buscar archivos que no estén actualmente abiertos en Excel.

Solución de problemas: Si algunos archivos no aparecen en los resultados de búsqueda, comprueba que estén totalmente cargados en Excel y que no estén en vista protegida ni en modo solo lectura, lo que podría limitar el acceso a la búsqueda.

Demostración: Buscar un valor en varios libros de trabajo abiertos con Kutools para Excel

Buscar un valor en varias hojas de cálculo utilizando fórmulas de Excel

En situaciones en las que tienes varios nombres de hoja conocidos en tu libro de trabajo y necesitas comprobar si un valor específico existe y dónde aparece entre esas hojas, puedes utilizar fórmulas de Excel para buscar de forma dinámica entre ellas. Este enfoque es especialmente útil cuando deseas que los resultados de búsqueda se mantengan actualizados automáticamente y tu listado de hojas es relativamente estable o lo gestionas en una tabla aparte.

Este método requiere que ya conozcas o dispongas de un listado con los nombres de todas las hojas a buscar. Es más efectivo para comprobaciones automáticas, paneles de control o siempre que desees crear una referencia resumen sin necesidad de ejecutar un escaneo completo cada vez.

Ventajas: Los resultados se actualizan automáticamente a medida que cambian los datos; no necesitas scripts ni complementos; todo se gestiona directamente dentro del libro de trabajo.
Desventajas: No es adecuado si los nombres de las hojas cambian con frecuencia o si tienes un número muy elevado de hojas.

Ejemplo de escenario: Supón que tienes tres hojas llamadas Sheet1, Sheet2 y Sheet3. Quieres saber en cuál o cuáles de esas hojas aparece un valor específico, como "Invoice123", en la celda A1, o simplemente comprobar si el valor existe en alguna de ellas.

Paso1. Supón que tienes una lista de nombres de hoja en D2:D4 (D2: Sheet1, D3: Sheet2, D4: Sheet3). Ingresa el valor a buscar (por ejemplo, "Invoice123") en E1. Después, en F2, introduce esta fórmula:

=IF(COUNTIF(INDIRECT("'"&D2&"'!A:A"), $E$1) >0, "Found", "Not Found")

Paso2. Arrastra la fórmula desde F2 hasta F4 para comprobar todas las hojas listadas en D2:D4. Esto te devolverá "Encontrado" o "No encontrado" para cada hoja.

Cómo funciona: La fórmula utiliza INDIRECTO para crear una referencia a cada hoja de cálculo listada y CONTAR.SI para verificar si el valor en E1 aparece en la columna A de cada hoja. Si lo necesitas, adapta el rango A:A para abarcar otra columna o un rango específico (por ejemplo, A1:Z100).

Consejo adicional: Para obtener el nombre de la(s) hoja(s) que contiene(n) el valor, utiliza la siguiente fórmula de matriz (introducida con Ctrl + Mayús + Intro en versiones antiguas de Excel o como fórmula normal en Microsoft365/Excel2021+):

=TEXTJOIN(", ",TRUE,IF(COUNTIF(INDIRECT("'"&D2:D4&"'!A:A"), $E$1)>0, D2:D4, ""))

Esto devolverá una lista separada por comas con todos los nombres de hojas donde se encuentre el valor. Ten presente que INDIRECTO solo funciona con libros de trabajo abiertos y no permite buscar en archivos cerrados.

Precauciones: Si los nombres de las hojas cambian o se eliminan, la fórmula devolverá un error #¡REF!; verifica siempre que la lista de hojas sea correcta. En libros de trabajo grandes, las fórmulas basadas en INDIRECTO pueden ralentizar el rendimiento.

Solución de problemas: Si ves errores, comprueba que todas las hojas referenciadas existen y que el rango de búsqueda es correcto. Si tu lista de hojas es dinámica, considera utilizar rangos con nombre o Validación de datos para mantener la lista actualizada automáticamente.

Artículos relacionados:

• ¿Cómo buscar y reemplazar rápidamente en varias hojas de cálculo o en todo el libro de trabajo?
• ¿Cómo buscar y reemplazar rápidamente en varios archivos de Excel abiertos?
• ¿Cómo encontrar el valor negativo más grande (menor que0) en Excel?
• ¿Cómo extraer todos los duplicados de una columna en Excel?