Las 11 Mejores Alternativas a BUSCARV (¡Actualizadas en 2022!) – Excel y Google Sheets
Descargar el Libro de Ejemplo
Este tutorial demostrará las mejores alternativas de BUSCARV en Excel y Google Sheets.
1. Función BUSCARX
Si tiene una nueva versión de Excel, entonces la función BUSCARX es probablemente la mejor alternativa a la función BUSCARV.
BUSCARX: Búsqueda a la izquierda
La matriz de búsqueda y la matriz de retorno de la función BUSCARX son argumentos separados. Esto conlleva las siguientes ventajas:
- No es necesario ajustar la fórmula cuando se insertan o eliminan columnas.
- No es necesario introducir rangos/arreglos de columnas múltiples cuando las columnas de búsqueda y de retorno no son adyacentes. Esto reduce la cantidad de datos a procesar.
- Poder realizar búsquedas a la izquierda (ver ejemplo):
=BUSCARX(F3;D3:D7;C3:C7)
BUSCARX: Devuelve más Columnas
Una característica de la función BUSCARX es poder devolver más de una columna.
=BUSCARX(F3;B3:B7;C3:D7)
Nota: El array de retorno (columnas) debe ser contiguo.
BUSCARX: Manejo de errores #N/D
A diferencia de la función BUSCARV, en la que necesitamos utilizar las funciones SI.ERROR o SI.ND para manejar el error #N/D, la función BUSCARX tiene un manejador de error #N/D incorporado, que es el cuarto argumento (if_not_found).
=BUSCARX(E3;B3:B7;C3:C7;"No Encontrado!")
BUSCARX: Opciones de modo de coincidencia
Otra gran característica de la función BUSCARX es poder seleccionar match_mode (5º argumento): 0 – Coincidencia exacta, -1 – Coincidencia exacta o siguiente elemento más pequeño, 1 – Coincidencia exacta o siguiente elemento más grande y 2 – Coincidencia de caracteres comodín.
=BUSCARX(E3;B3:B7;C3:C7;"Sin descuento!";-1)
Nota: A diferencia de las coincidencias aproximadas de las funciones BUSCARV, BUSCAR y COINCIDIR, en las que es necesario ordenar los datos, el modo de coincidencia de la función BUSCARX, por defecto, no requiere ordenar los datos (véase el ejemplo).
BUSCARX: Buscar último
El ejemplo modo_de_coincidencia de la sección anterior no requiere un conjunto de datos ordenados porque el valor por defecto del modo_de_búsqueda (6to argumento) es una búsqueda lineal (del primero al último). Ahora, podemos seleccionar el proceso de búsqueda de nuestro lookup (es decir, búsqueda lineal y búsqueda binaria), y una de ellas es la opción «Búsqueda del último al primero» (es decir, -1), que nos permite devolver fácilmente la última coincidencia.
=BUSCARX(E3;B3:B7;C3:C7;;;-1)
BUSCARX: Coincidencia Binaria-Exacta y Arrays Dinámicos
La actualización más importante de la función BUSCARV o incluso de la fórmula INDICE-COINCIDIR es poder realizar una coincidencia exacta con un proceso de búsqueda binaria. Ahora podemos utilizar la velocidad de la búsqueda binaria sin sacrificar la coincidencia exacta con sólo establecer el último argumento, modo_de_búsqueda, a 2 (orden ascendente) o -2 (orden descendente).
Veamos un escenario de búsqueda de un millón:
=BUSCARX(C3:C1000002;B3:B1000002;B3:B1000002;;;2)
Nota: Por defecto, el modo_de_coincidencia es 0, que es Coincidencia Exacta. Con la búsqueda binaria, podemos realizar dicha búsqueda en menos de 5 segundos. Otra cosa es que podemos convertir la función BUSCARX en una fórmula de matriz dinámica.
Una forma es introducir una entrada de matriz en el valor de búsqueda (por ejemplo, C3:C1000002).
2. INDICE-COINCIDIR
La fórmula INDICE-COINCIDIR es la alternativa más conocida a la función BUSCARV en versiones anteriores de Excel.
INDICE-COINCIDIR: Búsqueda a la izquierda
Al igual que con la función BUXCARX, la columna de búsqueda y la columna de retorno están separadas en la fórmula INDICE-COINCIDIR. La columna de búsqueda se introduce en la Función COINCIDIR, que también realiza el proceso de búsqueda, mientras que la columna de retorno se introduce en la Función INDICE, que devuelve el valor que corresponde al resultado de la Función COINCIDIR. Esta disposición proporciona los mismos beneficios mencionados en la Función BUXCARX: ajuste automático a las inserciones y eliminaciones de columnas, fórmula más rápida y eficiente debido a la menor entrada de matrices de columnas y poder realizar búsquedas a la izquierda (véase el ejemplo siguiente).
=INDICE(C3:C7;COINCIDIR(F3;D3:D7;0))
Veamos la fórmula:
Función COINCIDIR
Empecemos por encontrar la coordenada relativa de la fila del valor de búsqueda (por ejemplo, ID Estudiante) de la matriz de búsqueda (por ejemplo, D3:D7) utilizando la función COINCIDIR.
=COINCIDIR(F3;D3:D7;0)
Nota: El tipo de coincidencia (tercer argumento) de la función COINCIDIR define el modo de coincidencia. Cero significa coincidencia exacta, mientras que 1 y -1 son coincidencias aproximadas.
Función INDICE
Una vez que tenemos la coordenada de la fila, podemos utilizarla para devolver un valor del array de retorno correspondiente (por ejemplo, C3:C7) utilizando la función INDICE.
=INDICE(C3:C7;H3)
Nota: La función INDEX devuelve un valor de un array dadas las coordenadas relativas de la fila y la columna (para el array 2D). Combinando las dos fórmulas se obtiene nuestra fórmula original:
=INDICE(C3:C7;COINCIDIR(F3;D3:D7;0))INDICE-COINCIDIR: Array de búsqueda horizontal
Otro ejemplo que muestra la flexibilidad de la fórmula INDICE-COINCIDIR es cuando la matriz de búsqueda y la matriz de retorno están en orientaciones opuestas:
=INDICE(B3:B7;COINCIDIR(D7;E2:I2;0))
3. Función BUSCARH
Ahora que estamos hablando de orientaciones horizontales, también hay una función de búsqueda que se construye para las búsquedas horizontales – la función BUSCARH.
=BUSCARH(I3;C2:G3;2;FALSO)
Nota: La función BUSCARH funciona de la misma manera que la función BUSCARV pero en la orientación opuesta. Busca la coincidencia exacta en la primera fila y devuelve el valor correspondiente a partir de un indicador_filas dado.
4. DESREF-COINCIDIR: Referencia dinámica de columnas
Otra alternativa a BUSCARV es la fórmula DESREF-COINCIDIR, que funciona de forma similar a la fórmula INDICE-COINCIDIR.
Veamos un escenario de buscar con referencia de columna dinámica y veamos cómo el DESREF difiere ligeramente del INDICE-COINCIDIR.
=DESREF(B2;COINCIDIR(F3;B3:B7;0);COINCIDIR(G3;C2:D2;0))
Repasemos la fórmula:
Coordenadas de filas y columnas
En primer lugar, tenemos que determinar las coordenadas relativas de la fila y la columna utilizando la función COINCIDIR.
Función DESREF
En lugar de utilizar la Función INDICE para devolver un valor, la sustituiremos por la Función DESREF. Por defecto, la Función DESREF devuelve un rango definiendo las coordenadas relativas de fila y columna desde un rango de referencia (por ejemplo, B2).
=DESREF(B2;I3;J3)
Nota: La función DESREF es una función volátil, lo que significa que siempre recalculará cuando la hoja se recalcule. Dependiendo del escenario, esto puede afectar a la velocidad de su hoja.
Combinando todas las funciones se obtiene nuestra fórmula original
=DESREF(B2;COINCIDIR(F3;B3:B7;0);COINCIDIR(G3;C2:D2;0))5. INDIRECTI-DIRECCION-COINCIDIR: Referencia dinámica de columna
Otra alternativa flexible al BUSCARV es la fórmula INDIRECTO-DIRECCION-COINCIDIR. Apliquémosla al escenario de referencia de columna dinámica:
=INDIRECTO(DIRECCION(COINCIDIR(F3;B1:B7;0);COINCIDIR(G3;A2:D2;0)))
Repasemos la fórmula:
Coordenada de fila
En esta fórmula, debemos determinar la coordenada de la fila de la propia celda, a diferencia de las fórmulas INDICE y DESREF en las que utilizamos coordenadas relativas.
=COINCIDIR(F3;B1:B7;0)
Coordenada de columna
A continuación, también tenemos que determinar la coordenada de la columna de la celda.
=COINCIDIR(G3;A2:D2;0)
Función DIRECCION
A continuación, utilizamos las coordenadas de la fila y la columna para devolver una referencia de celda en formato de texto utilizando la función DIRECCION. Por defecto, la referencia de la celda de texto estará en forma absoluta (por ejemplo, $C$4).
=DIRECCION(I3;J3)
Función INDIRECTO
Por último, convertimos la referencia de la celda de texto en una referencia de celda real utilizando la función INDIRECTO.
=INDIRECTO(K3)
Nota: La función INDIRECTO es una función volátil, lo que significa que siempre recalculará cuando la hoja se recalcule. Dependiendo del escenario, esto puede afectar a la velocidad de su hoja. Combinando todas las funciones juntas se obtiene nuestra fórmula original:
=INDIRECTO(DIRECCION(COINCIDIR(F3;B1:B7;0);COINCIDIR(G3;A2:D2;0)))6. Función BUSCAR: Última coincidencia
Si hay entradas duplicadas en la matriz de búsqueda, obtener la última coincidencia es difícil para la función BUSCARV. La función BUSCARX es la mejor solución, pero si eso no es una opción, la función BUSCAR es la mejor alternativa.
=BUSCAR(2;1/(B3:B7=E3);C3:C7)
Repasemos la fórmula:
Condición de búsqueda
En primer lugar, comprobemos los valores de la columna de búsqueda (por ejemplo, B3:B7) con el valor de búsqueda (por ejemplo, E3).
=B3=$E$3
Recíproco de valores booleanos
A continuación, tomamos el recíproco de los valores booleanos, donde VERDADERO es 1 y FALSO es 0.
=1/G3
El array recíproco se utiliza como array de búsqueda para la función BUSCAR, que ignora los errores. Por lo tanto, técnicamente tenemos una matriz de 1s.
Función BUSCAR
La función BUSCAR sólo puede hacer una coincidencia aproximada con la suposición de que los datos están ordenados de forma ascendente, lo que significa que la función BUSCAR encontrará el valor más grande de la matriz de búsqueda que sea menor o igual al valor de búsqueda.
Usamos 2 como valor de búsqueda para aprovechar la coincidencia aproximada en el array de 1s. Si hay duplicados, como en el ejemplo, se devuelve la posición de la última instancia en su lugar.
=BUSCAR(2;H3:H7;C3:C7)
Combinando todas las fórmulas se obtiene nuestra fórmula original:
=BUSCAR(2;1/(B3:B7=E3);C3:C7)7. Función FILTRAR: Buscar todos los duplicados
Si queremos buscar todos los duplicados, las nuevas versiones de Excel ofrecen una alternativa mejor: la función FILTRAR. Es sencilla y no requiere muchos pasos como añadir columnas de ayuda.
=FILTRAR(C3:C7;B3:B7=E2)
Nota: El primer argumento es la matriz (por ejemplo, C3:C7) que queremos filtrar, y el segundo argumento es el criterio de filtrado (por ejemplo, B3:B7=E2).
8. FILTRAR-INDICE: Buscar la enésima coincidencia
En lugar de devolver todos los duplicados, podemos seleccionar la enésima coincidencia utilizando la fórmula FILTRAR-INDICE. Esta es una alternativa más conveniente que el método ID Único – BUSCARV (ver el artículo BUSCARV Valores Duplicados).
=INDICE(FILTRAR(C3:C7;B3:B7=E3);F3)
Repasemos la fórmula:
Función FILTRAR
Primero, devolvamos todos los duplicados usando la función FILTRAR. Usamos la matriz de búsqueda = valor de búsqueda como la condición del filtro.
=FILTRAR(C3:C7;B3:B7=E3)
Función INDICE y enésima coincidencia
A continuación, devolvemos la enésima coincidencia del resultado de la función FILTRAR utilizando la función INDICE.
=INDICE(H3:H5;F3)
Combinando todas las funciones se obtiene nuestra fórmula original
=INDICE(FILTRAR(C3:C7;B3:B7=E3);F3)9. SUMAPRODUCTO: Búsqueda de números
Si sólo buscamos números, podemos utilizar la función SUMAPRODUCT como alternativa a la función BUSCARV.
Una de las ventajas de la función SUMAPRODUCTO es la conveniencia de aplicar múltiples criterios. Para los usuarios de Excel que no tienen acceso a las nuevas Fórmulas de Matriz, no necesitarán usar Ctrl+Mayús+Intro para procesar las matrices. Veamos el siguiente ejemplo
=SUMAPRODUCTO((B3:B7=F3)*(C3:C7=G3)*D3:D7)
Repasemos la fórmula:
Condición 1
Primero, apliquemos las condiciones apropiadas a sus correspondientes columnas. Esta es la primera condición:
=B3=$F$3
Condición 2
Esta es la segunda condición:
=C3=$G$3
Array Y
A continuación, comprobamos si se cumplen ambas condiciones multiplicando las dos matrices booleanas (VERDADERO= 1 y FALSO = 0).
=I3*J3
Nota: La multiplicación de las matrices booleanas es equivalente a la función Y. Si se cumplen ambas condiciones, el resultado es 1. Si una de las condiciones es FALSA, el producto es 0.
Devolver Array
Si no hay duplicados, entonces la lista contendrá un valor de 1 y el resto son ceros. Convertimos esta matriz en la matriz de retorno multiplicando la matriz de retorno por
=K3*D3
Función SUMAPRODUCTO
La función SUMAPRODUCTO realiza una multiplicación de matrices y toma la suma de la matriz del producto. Como sólo hay un valor mayor que 0 y el resto son 0, la suma devolverá el valor que buscamos.
=SUMAPRODUCTO(L3:L7)
Nota: Hemos multiplicado las matrices antes de la función SUMAPRODUCTO para convertir las matrices booleanas en números.
La función SUMAPRODUCTO sólo realiza cálculos con números y excluye otros tipos de datos (por ejemplo, booleanos, cadenas).
Combinando todas las fórmulas se obtiene nuestra fórmula original
=SUMAPRODUCTO((B3:B7=F3)*(C3:C7=G3)*D3:D7)10. Función SUMAR.SI.CONJUNTO: Búsqueda de Números
En lugar de la función SUMAPRODUCTO, también podemos utilizar la función SUMAR.SI.CONJUNTO para realizar búsquedas de criterios múltiples en los números.
Es más simple y más conveniente de usar en comparación con la Función SUMAPRODUCTO, pero a diferencia de la Función SUMAPRODUCTO, no puede aceptar entradas de arreglos como los resultados de arreglos de otras funciones. El rango_suma y el rango_criterio son estrictamente rangos.
=SUMAR.SI.CONJUNTO(D3:D7;B3:B7;F3;C3:C7;G3)
Nota: El primer argumento es el rango_suma, que es la matriz que se sumará. Los argumentos siguientes son pares de rangos_de_criterios, donde se comprueban los criterios, y los criterios.
11. Hojas de cálculo de Google: Función QUERY
Aparte de la función BUSCARX, que no existe en Google Sheets, todas las funciones mencionadas anteriormente están disponibles y funcionan de la misma manera en Google Sheets, pero hay una alternativa más potente que podemos utilizar en Google Sheets: la función QUERY.
=QUERY(B3:C7; "SELECT C WHERE B='"&E3&"'")
Nota: El 2do argumento de la función QUERY es la sintaxis de la consulta (formato de texto) que puede ayudarnos a realizar manipulaciones de datos como búsquedas, ordenación, filtrado y formato. Veamos la fórmula:
SELECT
La cláusula SELECT filtra la columna que queremos devolver (por ejemplo, la columna C).
=QUERY(B3:C7; "SELECT C")
WHERE
La cláusula WHERE filtra la fila. Como el valor de la búsqueda es un texto, debemos encerrarlo entre comillas simples.
Nota: Si eliminamos la cláusula SELECT, se devolverán todas las columnas. Combinando ambas cláusulas se obtiene nuestra fórmula original
=QUERY(B3:C7; "SELECT C WHERE B='"&E3&"'")
