Si está aquí, significa que está intentando utilizar BUSCARV para recuperar varios valores de un conjunto de datos. Pero seamos claros, VLOOKUP NO PUEDE DEVOLVER VARIOS VALORES. Pero eso no significa que no podamos hacerlo. Podemos hacer una búsqueda que recupere múltiples valores en lugar del primero solamente.

286

Fórmula genérica

{=INDEX(array,SMALL(IF(lookup_value=lookup_value_range,ROW(lookup_value_range)-ROW(first cell of lookup_value_range)+1),ROW(1:1)))}

Matriz: el rango desde el que desea obtener datos.

lookup_value: su lookup_value que desea filtrar.

lookup_value_range: el rango en el que desea filtrar lookup_value.

La primera celda en el rango lookup_value: si su rango lookup_value es $ A $ 5: $ A $ 100 entonces es $ A $ 5.

Importante: Todo debe ser enlace: / excel-rango-nombre-referencia-absoluta-en-excel [referencia absoluta]. lookup_value puede ser relativo según el requisito.

Introdúzcalo como una fórmula de matriz. Después de escribir la fórmula, presione CTRL + SHIFT + ENTER para convertirla en una fórmula de matriz.

Ejemplo de búsqueda de resultados múltiples Tengo los datos de este estudiante en el rango A2: E14. En la celda G1, tengo un menú desplegable de valores de región, p. Ej. Central, Este, Norte, Sur y Oeste.

Ahora quiero, cualquiera que sea la región que tenga en G1, una lista de todos los estudiantes de esa región debería mostrarse en la columna H.

287

Para buscar varios valores en Excel, identifiquemos nuestras variables.

Array: $ C $ 2: $ C $ 14 lookup_value: $ G $ 1 lookup_value_range: $ A $ 2: $ A $ 14 La primera celda del rango lookup_value: $ A $ 2 De acuerdo con los datos anteriores, nuestra fórmula para recuperar múltiples valores en Excel será:

{=INDEX($C$2:$C$14,SMALL(IF($G$1=$A$2:$A$14,ROW($A$2:$A$14)-ROW($A$2)+1),ROW(1:1)))}

Copie esta fórmula en H2 y presione CTRL + MAYÚS + ENTRAR. Ahora arrastre hacia abajo esta fórmula hasta que obtenga un error #NUM. #NUM indica que no hay otros valores coincidentes restantes para ese valor_buscado, y es el final de la lista.

288

Si #NUM le molesta, puede tener el enlace: / fórmulas-lógicas-excel-iferror-function [función IFERROR] delante de la fórmula y mostrar información significativa en lugar del error.

{=IFERROR(INDEX($C$2:$C$14,SMALL(IF($G$1=$A$2:$A$14,ROW($A$2:$A$14)-ROW($A$2)+1),ROW(1:1))),"--List Ends--")}

Esto mostrará –List Ends– cuando se muestren todos los elementos.

Ahora, entendamos CÓMO FUNCIONA.

Aunque la fórmula puede parecer compleja, la idea es simple. Necesitamos obtener el número de índice de cada ocurrencia de valor, luego recuperar valores usando el`link: / lookup-formulas-excel-index-function [INDEX function] `de Excel.

Por lo tanto, el principal desafío es obtener una matriz de números de índice de lookup_value. Para obtener los números de índice, usamos link: / tips-if-condition-in-excel [IF] ʻand`link: / lookup-and-reference-excel-row-function [ROW]

funciones. La fórmula es realmente compleja en conjunto, vamos a analizarla.

Queremos obtener valores de la columna del estudiante, por lo que nuestra matriz para`link: / lookup-formulas-excel-index-function [INDEX function] `es $ C $ 2: $ C $ 14. Ahora tenemos que dar números de fila de $ A $ 2: $ A $ 14 (valor de búsqueda) en los que existe el valor de G1 (por ahora, digamos que G1 tiene una parte central).

SI ($ G $ 1 = $ A $ 2: $ A $ 14, FILA ($ A $ 2: $ A $ 14): Ahora, esta parte devuelve el número de fila si una celda cuenta el valor de G1 (central) en el rango $ A $ 2: $ A $ 14 más devuelve FALSO. En este ejemplo, devolverá

\ {2; FALSE; FALSE; FALSE; FALSE; 7; 8; FALSE; FALSE; 11; FALSE; FALSE; FALSE}.

  • Ahora, dado que la matriz anterior contiene el número de fila de la primera fila (1: 1) y necesitamos una fila a partir de nuestra matriz (A2: A14). Para hacerlo, usamos -ROW ($ A $ 2) +1 en la fórmula IF. Esto devolverá un número de filas antes de comenzar nuestra matriz.

Para este ejemplo, es -1. Si comenzara desde A3, devolvería -2 y así sucesivamente. número se restará de cada número en la matriz devuelta por IF. Entonces, finalmente IF ($ G $ 1 = $ A $ 2: $ A $ 14, FILA ($ A $ 2: $ A $ 14) -ROW ($ A $ 2) +1) esto se traducirá a \ {1; FALSE; FALSE; FALSE; FALSE; 6; 7; FALSE; FALSE; 10; FALSE; FALSE; FALSE}.

  • A continuación, esta matriz está rodeada por`link: / estadística-fórmulas-excel-small-function [PEQUEÑO] `

función. Esta función devuelve valor est en la matriz dada. Ahora, tenemos enlace: / fórmulas-estadísticas-excel-pequeña-función [PEQUEÑA] (\ {2; FALSO; FALSO; FALSO; FALSO; 7; 8; FALSO; FALSO; 11; FALSO; FALSO; FALSO} , FILA (1: 1)).

ROW (1: 1) devolverá 1. Por lo tanto, la función anterior devolverá el primer valor más pequeño en la matriz, que es 2.

Cuando copie esta fórmula en las celdas de abajo, FILA (1: 1) se convertirá en FILA (2: 2) y devolverá el segundo valor más pequeño en la matriz, que es 7 y así sucesivamente. Esto permite que la función devuelva primero el primer valor encontrado. Pero si primero desea obtener el último valor encontrado, utilice la función GRANDE en lugar de la función PEQUEÑA.

Ahora, utilizando los valores devueltos por encima de las funciones, la función INDICE devuelve fácilmente cada valor coincidente de un rango.

Entonces, sí, chicos, pueden BUSCAR múltiples valores correspondientes por un valor de búsqueda. En el gif anterior, he usado la función IFERROR para detectar errores y el formato condicional para hacerlo un poco visual. En el ejemplo anterior, he utilizado muchas otras funciones y técnicas que he explicado en otros artículos.

Descargar archivo: