Autor: Oscar Cronquist Artículo actualizado en enero 25, 2019

Este artículo muestra una fórmula de matriz que busca dos tablas en dos hojas diferentes y devuelve múltiples resultados. Sheet1 contiene table1 y sheet2 contiene table 2.

El valor de búsqueda es Pen y está en la celda B9, la fórmula encuentra dos coincidencias en la hoja 1 fila 3 y 6. Luego continúa en la hoja 2 y encuentra dos coincidencias, fila 3 y 6. Los valores adyacentes de cada coincidencia se devuelven al rango de celdas C9 en la hoja 1.

Fórmula de matriz en la celda C9:

=IFERROR(INDEX(tbl_1, SMALL(IF(($B$9=$B$3:$B$7), ROW($B$3:$B$7)-MIN(ROW($B$3:$B$7))+1, ""), ROW(A1)), 2), INDEX(tbl_2, SMALL(IF(($B$9=Sheet2!$B$3:$B$7), ROW(Sheet2!$B$3:$B$7)-MIN(ROW(Sheet2!$B$3:$B$7))+1, ""), ROW(A1)-SUM(--($B$9=$B$3:$B$7))), 2))

Cómo crear una fórmula matricial

  1. Copie (Ctrl + c) y pegue (Ctrl + v) la fórmula matricial en la barra de fórmulas.
  2. Mantenga presionado Ctrl + Shift.
  3. Presione Enter una vez.
  4. Suelte todas las teclas.

Cómo copiar una fórmula matricial

  1. Seleccionar celda C9
  2. Copiar (Ctrl + c)
  3. Seleccionar rango de celdas C9: C13
  4. Pegar (Ctrl + v)

Rango Nombrado

tbl_1 - Sheet1! B3: C7
tbl_2 - Sheet2! B3: C7

Cómo crear un rango con nombre

  1. Seleccionar rango de celdas Sheet1! B3: C7
  2. Escribe tbl_1 en el cuadro de nombre

Explicando la formula matricial

Paso 1 - ¿Qué valores son iguales al criterio?

El signo igual le permite crear una expresión lógica que compara el valor de celda en B9 con los valores en el rango de celda B3: B7, crea una matriz que contiene valores booleanos. Verdadero o falso.

$ B $ 9 = $ B $ 3: $ B $ 7

se convierte en

"Pluma" = {"Pluma"; "Borrador"; "Papel"; "Bolígrafo"; "Clip de papel"}

y devoluciones

{CIERTO; FALSO; FALSO; CIERTO; FALSO}

Paso 2 - Convertir matriz a números de fila

El Función IF tiene tres argumentos, el primero debe ser una expresión lógica. Si la expresión se evalúa como VERDADERA, sucede una cosa (argumento 2) y si FALSA sucede otra cosa (argumento 3).

Si la expresión lógica devuelve VERDADERO, la función SI reemplaza esos valores con los números de fila correspondientes, si FALSO devuelve "" (en blanco).

IF(($B$9=$B$3:$B$7), ROW($B$3:$B$7)-MIN(ROW($B$3:$B$7))+1, "")

se convierte en

SI ({VERDADERO; FALSO; FALSO; VERDADERO; FALSO}, FILA ($ B $ 3: $ B $ 7) -MIN (FILA ($ B $ 3: $ B $ 7)) + 1, "")

se convierte en

SI ({VERDADERO; FALSO; FALSO; VERDADERO; FALSO}, {3; 4; 5; 6; 7} -MIN ({3; 4; 5; 6; 7}) + 1, "")

se convierte en

SI ({VERDADERO; FALSO; FALSO; VERDADERO; FALSO}, {3; 4; 5; 6; 7} -3 + 1, "")

se convierte en

SI ({VERDADERO; FALSO; FALSO; VERDADERO; FALSO}, {1; 2; 3; 4; 5}, "")

y devuelve {1; ""; ""; 4; ""}

Paso 3 - Devuelve el k-ésimo número de fila más pequeño

Para poder devolver un nuevo valor en una celda cada uso el Pequeña función para filtrar los números de columna de menor a mayor. La función PEQUEÑO ignora el texto y los valores en blanco en la matriz, lo cual es muy útil en este caso.

SMALL(IF(($B$9=$B$3:$B$7), ROW($B$3:$B$7)-MIN(ROW($B$3:$B$7))+1, ""), ROW(A1))

se convierte en

PEQUEÑO ({1; ""; ""; 4; ""}, ROW (A1))

se convierte en

PEQUEÑO ({1; ""; ""; 4; ""}, 1)

y devuelve 1.

Paso 4: devuelve un valor o referencia de la celda en la intersección de una fila y columna en particular

El Función ÍNDICE devuelve un valor basado en una referencia de celda y un número de fila y un número de columna si es necesario.

INDEX(tbl_1, SMALL(IF(($B$9=$B$3:$B$7), ROW($B$3:$B$7)-MIN(ROW($B$3:$B$7))+1, ""), ROW(A1)), 2)

se convierte en

ÍNDICE (tbl_1,1, 2)

se convierte en

ÍNDICE ({"Lápiz", 1,5; "Borrador", 2; "Papel", 1,7; "Lápiz", 1,7; "Clip de papel", 3}, 1, 2)

y devuelve $ 1,5

Paso 5: devuelve otro valor si la expresión es un error

El Función de IFERROR devuelve value_if_error si la expresión es un error y el valor de la expresión misma de lo contrario

IFERROR (valor, valor_si_error)

Tenga en cuenta que la función IFERROR detecta todo tipo de errores en su fórmula. Use esta función con precaución.

La función IFERROR atrapa los errores y comienza a buscar valores en tbl_2

Descargar el archivo de Excel


Vlookup-through-multiple-sheet.xlsx


Búsqueda en el complemento de varias hojas

Buscar en varias hojas es un complemento para Excel 2007 / 2010 / 2013 (¡no Mac!) que le permite buscar un valor o varios valores y devolver múltiples valores o filas de varias hojas.

Caracteristicas

  • Función personalizada fácil de usar
  • Búsquedas en varias hojas
  • Puedes usar múltiples valores de búsqueda al mismo tiempo
  • Usa comodines para refinar tus búsquedas aún más
  • Hasta 127 múltiples rangos u hojas
  • Devuelve todos los valores o filas
  • Devuelve valores únicos únicos o filas
  • Devuelve valores duplicados o filas

Lo que obtienes

  • Busque en el complemento de varias hojas para el archivo Excel 2007 / 2010 / 2013 * .xlam.
  • Busque en el complemento de varias hojas para el archivo Excel 2003 * .xla.
  • Instrucciones sobre cómo instalar.
  • Instrucciones sobre cómo utilizar la función personalizada.
  • Excel * .xlsx archivo de ejemplo.
  • Excel 2003 * .xls archivo de ejemplo.
  • Licencias 2, computador hogar y oficina.
  • Puedes comprar el archivo fuente VBA por $ 10 más.

Ejemplos

Ejemplo 1 - Búsqueda una valor en dos hojas y devuelve varias filas

Ejemplo 2 - Búsqueda una Valor en dos hojas y retorno múltiple. único distinto filas

Ejemplo 3 - Búsqueda una Valor en dos hojas y retorno múltiple. duplicar filas

Busque varias hojas y devuelva filas duplicadas

Ejemplo 4 - Búsqueda dos valores en dos hojas y devolver todas las filas coincidentes

valores de búsqueda en varias hojas

Ejemplo 5 - Búsqueda dos Valores en dos hojas y retorno. único distinto filas

Ejemplo 6 - Búsqueda dos Valores en dos hojas y retorno. duplicar filas

Ejemplo 7 - Búsqueda dos valores utilizando comodines en dos hojas y devolver todas las filas coincidentes

Ejemplo 8 - Búsqueda dos valores usando comodines en dos hojas y devuelve multiples único distinto filas

Ejemplo 9 - Búsqueda dos Valores en dos hojas usando comodines y retorno múltiple. duplicar filas

Ejemplo 10 - Ejemplos de comodines

Cómo utilizar la función personalizada.

Comprar Búsqueda en el complemento de varias hojas (Excel 2003 / 2007 / 2010 / 2013) - Precio $ 19 USD

Añadir a la cestaVER CESTA

Preguntas:

En todos los ejemplos anteriores, busca un valor en la primera columna. ¿Puede el complemento buscar en otras columnas?

¡Sí, por supuesto! Usted elige en qué columna buscar, hacer clic y leer Cómo utilizar la función personalizada. anterior.

¿Cuáles son los valores distintos únicos?

Se eliminan todos los valores, pero los valores duplicados.

¿Puedo buscar mayúsculas y minúsculas?

No, es un caso insensible.

¿Cómo entro en esta función definida por el usuario?

Es una fórmula matricial.

    1. Escriba la función definida por el usuario en la barra de fórmulas.
    2. Mantenga presionado Ctrl + Shift.
    3. Presione Enter una vez.
    4. Suelte todas las teclas.

¿Hay una garantía de devolución de dinero?

Claro, usted tiene garantía de devolución de dinero sin condiciones para los días de 14.

¿Puedo ver el código fuente de vba?

No, está bloqueado para ver, pero puede comprar un archivo fuente VBA por $ 40 más.

Tengo más preguntas?

Utilizar esta Formulario de contacto dejarme saber.

Testimonios
El complemento funciona perfectamente y definitivamente me ahorró mucho tiempo y esfuerzo. Gracias por responder mis preguntas.

HL

Comprar Búsqueda en el complemento de varias hojas (Excel 2003 / 2007 / 2010 / 2013) - Precio $ 19 USD
Añadir a la cestaVER CESTA