Cómo fusionar dos hojas de Google: buscar, comparar y actualizar registros

La entrada del blog de hoy presenta todas las formas de fusionar 2 hojas de Google. Utilizaremos VLOOKUP, INDEX/MATCH, QUERY y el complemento Merge Sheets para actualizar las celdas de una hoja a partir de los registros de otra, basándonos en las coincidencias de las columnas comunes.

  • Cómo combinar hojas de Google mediante la función VLOOKUP
  • Combinar hojas de Google utilizando el dúo INDEX MATCH
  • Combinar hojas de Google y actualizar celdas con la función QUERY
  • Combinación de tablas de diferentes hojas de cálculo de Google: función IMPORTRANGE
  • Manejar los errores devueltos por la función INDEX MATCH en Google Sheets

Combinar hojas de Google con la función VLOOKUP

Lo primero a lo que puedes recurrir cuando necesites hacer coincidir y fusionar dos hojas de Google es la función VLOOKUP.

Sintaxis y uso

Esta función busca en una columna que especifiques un determinado valor clave y extrae uno de los registros relacionados de la misma fila en otra tabla u hoja.

Aunque el VLOOKUP de Google Sheets suele considerarse una de las funciones más difíciles, en realidad es bastante sencillo e incluso fácil una vez que se conoce.

Echemos un vistazo a sus componentes:

=VLOOKUP(clave_búsqueda, rango, índice, [is_sorted])

  • search_key es el valor clave que está buscando. Puede ser cualquier cadena de texto, número o una referencia de celda.
  • El rango es el grupo de celdas (o una tabla) donde buscarás la search_key y de donde sacarás los registros relacionados.
    Nota. VLOOKUP en Google Sheets siempre busca la clave de búsqueda en la primera columna del rango.
  • El índice es el número de la columna dentro de ese rango de donde quieres sacar los datos.

    Por ejemplo, si tu rango a buscar es A2:E20 y es la columna E de la que necesitas obtener los datos, introduce 5. Pero si el rango es D2:E20, tendrás que introducir 2 para obtener los registros de la columna E.

  • [is_sorted] es el único argumento que puede omitir. Se utiliza para decir si la columna con valores clave está ordenada (TRUE) o no (FALSE). Si es TRUE, la función trabajará con la coincidencia más cercana, si es FALSE – con una completa. Si se omite, se utiliza TRUE por defecto.

Consejo. Tenemos una guía detallada dedicada a VLOOKUP en Google Sheets. Por favor, consúltala para aprender más sobre la función, sus peculiaridades y límites, y obtener más ejemplos de fórmulas.

Teniendo en cuenta estos argumentos, vamos a utilizar VLOOKUP para combinar dos hojas de Google.

Supongamos que tengo una pequeña tabla con bayas y sus identificaciones en la Hoja2. Sin embargo, se desconoce la disponibilidad de las existencias:
My main table to fill in where data is missing from column C.
Llamemos a esta tabla la principal ya que mi objetivo es rellenarla.

También hay otra tabla en la Hoja1 con todos los datos, incluyendo la disponibilidad de stock:
My lookup table with data.
La llamaré la tabla de búsqueda ya que miraré en ella para obtener los datos.

Utilizaré la función VLOOKUP de Google Sheets para fusionar estas 2 hojas. La función hará coincidir las bayas en ambas tablas, y sacará la información correspondiente de la búsqueda en la tabla principal.

=VLOOKUP(B2,Hoja1!$B$2:$C$10,2,FALSE)
Match and merge data using VLOOKUP in Google Sheets.
Así es como esta fórmula fusiona dos hojas de Google exactamente:

  • Busca el valor de B2 (hoja principal) en la columna B de la Hoja1 (hoja de búsqueda).
    Nota. Recuerda que VLOOKUP escanea la 1ª columna del rango especificado – ¡Hoja1!$B$2:$C$10.
    Nota.
  • Utilizo referencias absolutas para el rango porque copio la fórmula hacia abajo en la columna y por lo tanto necesito que este rango permanezca igual en cada fila para que el resultado no se rompa.

  • FALSE al final dice que los datos de la columna B (en la hoja de búsqueda) no están ordenados por lo que sólo se considerarán las coincidencias exactas.
  • Una vez que hay una coincidencia, el VLOOKUP de Google Sheets extrae el registro relacionado de la segunda columna de ese rango (columna C).
  • Ocultar los errores devueltos por VLOOKUP en Google Sheets – IFERROR

    ¿Pero qué pasa con esos errores #N/A?

    Los ves en esas filas donde las bayas no tienen coincidencias en otra hoja y no hay nada que devolver. Por suerte, hay una manera de mantener esas celdas vacías.

    Sólo tienes que envolver tu VLOOKUP de Google Sheets en IFERROR

    =IFERROR(VLOOKUP(B2,Hoja1!$B$2:$C$10,2,FALSE),N”N”)
    Hide errors using the IFERROR function.

    Consejo. Atrapa y corrige otros errores que tu VLOOKUP de Google Sheets pueda devolver utilizando las soluciones de esta guía.

    Comparar y actualizar los registros de toda la columna a la vez – ArrayFormula

    Una cosa más que me gustaría mencionar es cómo hacer coincidir y combinar los datos de Google Sheets para toda la columna a la vez.

    No es nada del otro mundo, sólo una función más – ArrayFormula.

    Simplemente reemplaza tu registro clave de una celda en el VLOOKUP de Google Sheets con toda la columna y pon esta fórmula completa dentro de ArrayFormula:

    =ArrayFormula(IFERROR(VLOOKUP(B2:B10,Hoja1!$B$2:$C$10,2,FALSE),N”N”))
    VLOOKUP in the ArrayFormula to get results for all cells in the column.
    De esta manera, no tendrás que copiar la fórmula en la columna. ArrayFormula devolverá el resultado correcto a cada celda de inmediato.

    Consejo. Encuentre más detalles sobre ArrayFormula en esta entrada del blog.

    Aunque el VLOOKUP en las Hojas de cálculo de Google es perfecto para tareas tan simples, tiene algunos límites. Este es uno de los inconvenientes: no puede mirar a su izquierda. Cualquiera que sea el rango que indiques, siempre explora su primera columna.

    Por lo tanto, si usted necesita para combinar 2 hojas de Google y tirar de los ID (datos de la primera columna) sobre la base de las bayas (segunda columna), VLOOKUP no ayudará. Simplemente no podrás construir una fórmula correcta.

    En casos como este, INDEX MATCH para Google Sheets entra en el juego.

    Combinar y fusionar hojas de Google usando el dúo INDEX MATCH

    INDEX MATCH, o más bien INDEX & MATCH, son en realidad dos funciones diferentes de Google Sheets. Pero cuando se utilizan juntas, son como un VLOOKUP de nivel superior.

    Sí, también fusionan las hojas de Google: actualizan las celdas de una tabla con registros de otra tabla basándose en registros clave comunes.

    Pero lo hacen mucho mejor ya que ignoran todas esas limitaciones que tiene VLOOKUP.

    No voy a cubrir todos los fundamentos hoy porque ya lo hice en esta entrada del blog. Pero te daré algunos ejemplos de fórmulas INDEX MATCH para que puedas ver cómo funcionan directamente en las hojas de cálculo de Google. Utilizaré las mismas tablas de ejemplo de arriba.

    INDEX MATCH en acción en Google Sheets

    En primer lugar, vamos a fusionar esas hojas de Google y actualizar la disponibilidad de las existencias de todas las bayas que coincidan:

    =INDEX(Hoja1!$C$1:$C$10,MATCH(B2,Hoja1!$B$1:$B$10,0))
    Update data using INDEX MATCH in Google Sheets.
    ¿Cómo funcionan INDEX y MATCH cuando se usan juntos de esta manera?

  • MATCH mira a B2 y busca exactamente el mismo registro en la columna B de la Hoja1. Una vez encontrado, devuelve el número de la fila que contiene ese valor – 10 en mi caso.
  • INDEX va a esa décima fila en la Hoja1 también, sólo que toma el valor de otra columna – C.
  • Ahora vamos a probar INDEX MATCH con lo que el VLOOKUP de Google Sheets no puede hacer: fusionar hojas y actualizar la columna más a la izquierda con los IDs requeridos:

    =INDEX(Hoja1!$A$2:$A$10,MATCH(B2,Hoja1!$B$2:$B$10,0))
    INDEX MATCH updates the leftmost column.
    Muy fácil 🙂

    Manejar los errores devueltos por INDEX MATCH en Google Sheets

    Vamos a ir más allá y a deshacernos de esos errores en las celdas sin coincidencias. IFERROR nos ayudará de nuevo. Simplemente pon tu INDEX MATCH de Google Sheets como primer argumento.

    Ejemplo 1.

    =IFERROR(INDEX(Hoja1!$C$1:$C$10,MATCH(B2,Hoja1!$B$1:$B$10,0)),N-“N-“)
    Incorporate IFERROR to have empty cells instead of errors.

    Ejemplo 2.

    =IFERROR(INDEX(Hoja1!$A$2:$A$10,MATCH(B2,Hoja1!$B$2:$B$10,0)),N”N”)
    IFERROR for the leftmost column.
    Ahora, ¿cómo se combinan esas hojas de Google utilizando INDEX MATCH y se actualizan todas las celdas de la columna completa a la vez?

    Pues… no lo haces. Hay un pequeño problema: ArrayFormula no funciona con estas dos.

    Tendrás que copiar la fórmula INDEX MATCH en la columna o utilizar la función QUERY de Google Sheets como alternativa.

    Combinar hojas de Google y actualizar celdas usando QUERY

    La función QUERY de Google Sheets es la más potente de las hojas de cálculo. Por ello, no es de extrañar que ofrezca una forma de fusionar tablas, es decir, de hacer coincidir y fusionar valores de diferentes hojas.

    =QUERY(datos, consulta, [cabeceras])
    Consejo. Si nunca has utilizado el QUERY de Google Sheets, este tutorial te permitirá conocer su peculiar lenguaje.

    ¿Cómo debería ser la fórmula de QUERY para actualizar la columna Stock con los datos reales?

    =QUERY(Hoja1!$A$2:$C$10,N “select C where B=’N”&Hoja4!$B2:$B$10&”N”N”)
    QUERY updates one column with the info from another sheet.

    • Google Sheets QUERY busca en mi hoja de búsqueda (Hoja1 con los registros que necesito tirar a mi tabla principal)
    • y devuelve todas las celdas de la columna C en las que la columna B coincide con las bayas de mi tabla principal

    Permíteme perder esos errores para las celdas sin coincidencias:

    =IFERROR(QUERY(Hoja1!$A$2:$C$10,”select C where B='”&Hoja4!$B2:$B$10&”‘”),””)
    Wrap QUERY in IFERROR to dispose of errors.
    Bueno, así está mejor 🙂

    Combinar tablas de diferentes hojas de cálculo de Google – Función IMPORTRANGE

    Hay una función más que me gustaría mencionar. Es importante porque te permite fusionar hojas que residen en diferentes hojas de cálculo de Google (archivos).

    La función se llama IMPORTRANGE:

    =IMPORTRANGE(N-“hoja de cálculo_url”,N-“rango_cadena”)

    • el primero va el enlace a la hoja de cálculo en la que se extraen los datos de
    • la segunda va la hoja y el rango que quieres tomar de esa hoja de cálculo

    Nota. Te recomiendo encarecidamente que revises los documentos de Google sobre esta función para que no te pierdas ningún matiz importante de su funcionamiento.

    Imagina que tu hoja de búsqueda (con los datos de referencia) está en la hoja de cálculo 2 (también conocida como hoja de cálculo de búsqueda). Tu hoja principal está en la Hoja de cálculo 1 (hoja de cálculo principal).

    Nota. Para que IMPORTRANGE funcione, debes conectar ambos archivos. Y aunque la Hoja de cálculo de Google sugiere un botón para ello justo después de escribir la fórmula en una celda y pulsar Intro, para las fórmulas que aparecen a continuación es posible que tengas que hacerlo antes. Esta guía paso a paso te ayudará.

    A continuación se muestran los ejemplos para combinar hojas de Google de diferentes archivos utilizando IMPORTRANGE con cada función que has aprendido hoy.

    Ejemplo 1. IMPORTRANGE + VLOOKUP

    Utiliza IMPORTRAGE como rango en VLOOKUP para fusionar 2 hojas de cálculo de Google separadas:

    =ArrayFormula(IFERROR(VLOOKUP(B2:B10,IMPORTRANGE(“https://docs.google.com/spreadsheets/d/1Sq…j7o/edit”,”¡Hoja1!$B$2:$C$10″),2,FALSE),N-“N”)
    Pull data from another Google file using IMPORTRANGE inside VLOOKUP.

    Ejemplo 2. IMPORTRANGE + INDEX MATCH

    Como en el caso de INDEX MATCH & IMPORTRANGE, la fórmula se vuelve más voluminosa ya que hay que referenciar otra hoja de cálculo dos veces: como rango para INDEX y como rango para MATCH:

    =IFERROR(INDEX(IMPORTRANGE(“https://docs.google.com/spreadsheets/d/1Sq…j7o/edit”,”Hoja1!$A$1:$A$10″),MATCH(B2,IMPORTRANGE(“https://docs.google.com/spreadsheets/d/1Sq…j7o/edit”,”Hoja1!$B$2:$B$10″),0)),N-“N-“.)
    IMPORTRANGE + INDEX MATCH in one formula.

    Ejemplo 3. IMPORTRANGE + QUERY

    Este tándem de fórmulas es mi favorito. Pueden hacer frente a casi cualquier cosa en las hojas de cálculo cuando se usan juntas. Fusionar hojas de Google desde hojas de cálculo separadas no es una excepción.

    =IFERROR(QUERY(IMPORTRANGE(“https://docs.google.com/spreadsheets/d/1Sq…j7o/edit”,”¡Hoja1!$A$2:$C$10″),”select Col3 where Col2='”&QUERY!$B2:$B$10&”‘”),N”N”)
    Import data from another spreadsheet using IMPORTRANGE and QUERY.
    ¡Uf!

    Eso es todo para las funciones y fórmulas.

    Usted es libre de elegir cualquier función y construir su propia fórmula por los ejemplos anteriores …

    o…

    …¡prueba una herramienta especial que fusiona hojas de Google por ti! 😉

    Manera libre de fórmulas para combinar datos – Complemento de fusión de hojas para Google Sheets

    Si no tienes tiempo para construir o incluso aprender fórmulas, o si simplemente estás buscando la forma más fácil de unir datos basados en registros comunes, Merge Sheets será perfecto.

    Todo lo que tendrás que hacer es marcar las casillas de verificación en 5 pasos fáciles de usar:

  • seleccione su hoja principal
  • seleccione su hoja de búsqueda
  • marque las columnas clave (las que contienen registros que deben coincidir) con casillas de verificación
  • elija las columnas que desea actualizar:
    Select columns to update.
  • ajustar las opciones adicionales, por ejemplo, marcar los registros actualizados con color o en una columna de estado, etc.
  • There is even a possibility to save all the selected options into a scenario and reuse it whenever you need:
    Save the options into the scenario to use later.
    Watch this 3-minute demo video to see how it works:

    {
    “@context”: “http://schema.org”,
    “@type”: “VideoObject”,
    “name”: “Merge Sheets in Google Sheets”,
    “description”: “Use Merge Sheets to keep your Google Sheets tables up-to-date. The add-on will scan two tables for matches and do a quick vlookup. It will update old records, add missing info, and pull new rows and columns to your main table. Learn more on our website: https://www.ablebits.com/google-sheets-add-ons/merge-sheets/index.php Get Merge Sheets at Google Sheets add-ons store: https://gsuite.google.com/marketplace/app/merge_sheets/850686067510”,
    “thumbnailUrl”: “https://i.ytimg.com/vi/V4DXNgqEdLc/default.jpg”,
    “uploadDate”: “2019-10-30T13:12:20Z”,
    “duration”: “PT2M56S”,
    “embedUrl”: “https://www.youtube.com/embed/V4DXNgqEdLc”,
    “interactionCount”: “10317”,
    “publisher”: {
    “@type”: “Organization”,
    “name”: “Ablebits.com”,
    “url”: “https://www.ablebits.com”
    },
    “author”: {
    “@type”: “Person”,
    “name”: “Irina Pozniakova”,
    “url”: “https://www.ablebits.com/office-addins-blog/author/irina-pozniakova/”
    }
    }
    I encourage you to install your Merge Sheets from the Google Sheets store and follow these instructions to try and update your own table with the info from another sheet.

    Spreadsheet with formula examples

    Merge Google sheets & update data – formula examples (make a copy of the file)

    You may also be interested in

    {
    “@context”: “https://schema.org”,
    “@type”: “Article”,
    “url”: “https://www.ablebits.com/office-addins-blog/merge-google-sheets/”,
    “mainEntityOfPage”: “https://www.ablebits.com/office-addins-blog/merge-google-sheets/”,
    “inLanguage”: “en-us”,
    “headline”: “Merge two Google sheets: lookup, match and update records”,
    “description”: “Today’s blog post features all ways to merge 2 Google Sheets. You will use VLOOKUP, INDEX/MATCH, QUERY and Merge Sheets add-on to update cells in one sheet from records from another one based on matches in common columns.”,
    “author”: {
    “@type”: “Person”,
    “name”: “Natalia Sharashova”,
    “url”: “https://www.ablebits.com/office-addins-blog/author/natalia-sharashova/”
    },
    “publisher”: {
    “@type”: “Organization”,
    “name”: “Ablebits.com”,
    “logo”: {
    “@type”: “ImageObject”,
    “url”: “https://cdn.ablebits.com/_img/d-19/logo/ablebits-logo-desktop.svg”
    }
    },
    “datePublished”: “2022-03-18”,
    “dateModified”: “2022-04-04”
    }

    Google Sheets: artículos destacados

    • Combinar datos de filas duplicadas en base a una columna única
    • Cómo comparar datos en dos hojas de Google o columnas
    • VLOOKUP de Google Sheets con ejemplos

    var b20CategorySlug = “merge-google-sheets”;

    Tabla de contenidos

    Scroll to Top