17 Razones Por Las Que Tu BUSCARX No Funciona
Descargar el Libro de Ejemplo
Este tutorial demostrará cómo depurar las fórmulas BUSCARX en Excel. Si su versión de Excel no soporta BUSCARX, lea cómo usar BUSCARV en su lugar.
La mayoría de los errores en nuestras fórmulas BUSCARX están relacionados con las propiedades y criterios de BUSCARX. La sintaxis de la Función BUSCARX sólo nos muestra los argumentos necesarios para realizar la función, pero no nos informa completamente de las propiedades y criterios que son necesarios para que funcione correctamente. Por lo tanto, en este tutorial, no sólo aprenderemos a diagnosticar errores de BUSCARX, sino que también entenderemos más sobre sus propiedades y criterios.
Error #N/D Error
Si la función BUSCARX no encuentra una coincidencia, devolverá el error #N/D. Vamos a diagnosticar el problema.
1. #N/D – No hay coincidencia exacta
Por defecto, la función BUSCARX busca una coincidencia exacta. Si el elemento no se encuentra dentro de la matriz de búsqueda, entonces devolverá el error #N/D.
2. #N/D – No hay coincidencia aproximada
Si el modo_de_coincidencia (es decir, el 5to argumento) se establece en -1, la función BUSCARX buscará primero la coincidencia exacta, pero si no hay coincidencia exacta, encontrará el mayor valor de la matriz de búsqueda que sea menor que el valor de búsqueda. Por lo tanto, si no hay una coincidencia exacta y todos los valores de la matriz de búsqueda son mayores que el valor de búsqueda, la función BUSCARX devolverá el error #N/D.
En lugar del valor más grande <= valor de búsqueda, también podemos buscar lo contrario, valor más pequeño >= valor de búsqueda, si establecemos el modo_de_coincidencia a 1. Esta última condición encontrará el valor más pequeño que sea mayor o igual que el valor de búsqueda, y si todos los valores son menores que el valor de búsqueda, se devuelve el Error #N/D:
Si el valor realmente no existe, entonces la fórmula funciona correctamente. Recomendamos añadir un manejo de errores para que si el valor no se encuentra, se emita un valor diferente en lugar del error #N/D:
=BUSCARX(G3;B3:B7;C3:C7;"No Coincide!";1)
Sin embargo, si el valor de búsqueda existe y la función BUSCARX no puede encontrarlo, aquí hay algunas razones posibles:
3. #N/D – Números almacenados como texto (y otros desajustes de tipo de datos)
Uno de los criterios importantes de BUSCARX es que los tipos de datos del valor de búsqueda y de la matriz de búsqueda deben ser los mismos. Si no es así, la función BUSCARX no podrá encontrar una coincidencia. El ejemplo más común de esto son los números almacenados como texto.
Una forma de resolver esto es utilizar la herramienta Texto a Columnas de Excel para convertir los números almacenados como texto en números. Estos son los pasos:
- Resalte las celdas y vaya a Datos > Herramientas de datos > Texto a columnas
- En la ventana emergente, seleccione Delimitado y haga clic en Siguiente.
- En el siguiente paso, seleccione Tabulador y haga clic en Siguiente.
- En el último paso, seleccione el tipo de datos requerido (por ejemplo, fecha) y el formato y haga clic en Finalizar.
- El rango se convertirá en el tipo de datos establecido (por ejemplo, fecha)
4. #N/D – Espacios adicionales
Las búsquedas de texto son propensas a errores debido a los espacios adicionales. En este ejemplo, el valor de búsqueda, «Sub 2», contiene dos espacios y, por lo tanto, no coincidirá con «Sub 2» (un espacio) de la matriz de búsqueda.
Una forma de resolver esto es utilizar la función ESPACIOS para eliminar los espacios adicionales. Podemos aplicarla tanto al valor de la búsqueda como al conjunto de la matriz de búsqueda.
=BUSCARX(ESPACIOS(E3);ESPACIOS(B3:B7);C3:C7)
Nota: La función ESPACIOS elimina los espacios extra entre las palabras hasta que haya un límite de un espacio entre ellas, y se eliminan los espacios antes de la primera palabra y después de la última palabra de un texto o frase.
5. #N/D – Matriz de búsqueda no ordenada
Si el 6to argumento (es decir, modo_de_busqueda) de la función BUSCARX se establece como 2 o -2, la función BUSCARX utilizará el método de búsqueda binaria para buscar el valor, y este método requiere un conjunto de datos ordenado (es decir, 2 – orden ascendente, -2 – orden descendente). Si el conjunto de búsqueda no está ordenado, la función BUSCARX devolverá un valor incorrecto o el error #N/D:
Para resolver el problema anterior, debemos ordenar los datos manualmente o mediante fórmulas:
Ordenar manualmente
- Resalte todos los datos y vaya a la pestaña Datos > Ordenar y filtrar > Haga clic en Ordenar.
- Aparecerá una ventana emergente. Seleccione la columna de la matriz de búsqueda y establezca el orden requerido (por ejemplo, ascendente o de más antiguo a más reciente para las fechas). Haga clic en Aceptar.
- Los datos se ordenarán en base a la matriz de búsqueda (por ejemplo, B3:B7). El BUSCARX se recalcula ahora y muestra el resultado correcto.
Ordenar utilizando la función ORDENAR y la función ORDENARPOR
También podemos utilizar la función ORDENAR y la función ORDENARPOR para ordenar la matriz de búsqueda (por ejemplo, B3:B7) y la matriz de retorno (por ejemplo, C3:C7), respectivamente.
=BUSCARX(E3;ORDENAR(B3:B7);ORDENARPOR(C3:C7;B3:B7);;-1;2)
Nota: Por defecto, tanto las funciones ORDENAR como ORDENARPOR ordenan un array en orden ascendente. La principal diferencia entre ambas es que la función ORDENAR siempre devolverá todas las columnas de un array mientras que la función ORDENARPOR puede devolver una columna específica (por ejemplo, C3:C7) de un array.
Error ¡#VALOR!
¡Si una entrada no satisface los criterios para realizar el BUSCARX, la función no funcionará y en su lugar devolverá el #Valor! Error.
6. ¡#VALOR! – Tamaños de fila no uniformes
Un requisito de la función BUSCARX (para una búsqueda vertical típica) es que los tamaños de las filas del array de búsqueda y del array de retorno deben ser iguales.
7. #¡VALOR! – Horizontal vs. Vertical
La matriz de retorno puede ser bidimensional y devolver múltiples columnas (o filas para una búsqueda horizontal). Sin embargo, el número de filas (o columnas) debe coincidir. Esta propiedad permite que la función BUSCARX devuelva más de una columna (o fila para una búsqueda horizontal), pero la consecuencia es que no podemos emparejar una matriz de retorno horizontal 1D con una matriz de búsqueda vertical 1D, a diferencia de la fórmula INDICE-COINCIDIR, en la que podemos hacer orientaciones opuestas:
=INDICE(E2:I2;COINCIDIR(D5;B3:B7;0))
Si hace esto, el BUSCARX aceptará el array de retorno como una entrada 2D con un tamaño de fila que no coincide con el tamaño de fila del array de búsqueda. ¡Por lo tanto, devuelve el #Valor! Error.
=BUSCARX(D5;B3:B7;E2:I2)
Para solucionar esto, podemos utilizar la función TRANSPONER para transponer la orientación de una de las matrices 1D:
=BUSCARX(D5;B3:B7;TRANSPONER(E2:I2))
Nota: La función TRANSPONER cambia las coordenadas relativas de fila y columna de una celda en una lista. En E2:I2, F2 es la fila 1, columna 2 relativa al rango. Por lo tanto, las coordenadas transpuestas serán fila 2, columna 1. G2 es la fila 1, columna 3 y se transpone a la fila 3, columna 1 y así sucesivamente.
8. #VALOR – Rango de valores
Los argumentos 5to (modo_de_coincidencia) y6º (modo de búsqueda) de la función BUSCARX deben tener entradas válidas. ¡Si no es así, el BUSCARX devolverá el error #Valor!.
Nota: El modo_de_coincidencia sólo puede aceptar 0, -1,1 y 2 mientras que el modo_de_búsqueda sólo puede aceptar 1,-1,2 y-2.
Error #¿NOMBRE?
El error #¿NOMBRE? es provocado por:
- Error al escribir el nombre de la función
- Error al escribir una referencia (referencia de libro/hoja y rangos nombrados).
- Un rango con nombre inexistente
- Texto no entrecomillado.
9. #NOMBRE DE LA FUNCIÓN – Nombre de la función mal escrito
Si hay un error tipográfico en el nombre de una función, Excel devolverá el error #NOMBRE?.
10. #EL NOMBRE DE LA FUNCIÓN NO EXISTE – El rango nombrado no existe
El error #NOMBRE? También puede ser causado por un rango con nombre indefinido en la fórmula. O bien el rango con nombre no existe realmente o hay un error tipográfico en el nombre.
Nota: Hay dos rangos con nombre en la hoja anterior: Suscription y Pric. La errata en el rango con nombre Precio da lugar a un rango con nombre que no existe, y cualquier texto que no esté entre comillas se considera un rango con nombre, lo que también puede dar lugar al error #NOMBRE?.
11. #NOMBRE DE LA FUNCIÓN – Error de referencia del libro de trabajo/hoja
Cuando los nombres de los libros/hojas contienen espacios y caracteres especiales, excepto el guión bajo, es necesario encerrar la referencia del libro/hoja entre comillas simples. Si esto no se cumple, Excel no puede reconocer la referencia del libro/hoja y devolverá el error #NAME? Error.
ERROR #¡DESBORDAMIENTO!
¡También hay un conjunto de criterios cuando se devuelve una salida de matriz, y si esos criterios no se satisfacen, se devuelve el error SPILL! En lugar de la salida de la matriz.
12. #¡DESBORDAMIENTO! – Bloque de Derrame
Una fórmula de matriz dinámica no sobrescribirá los valores que están dentro de su rango de derrame. ¡En su lugar, la salida del array se bloqueará, y se devolverá el mensaje de error #DESBORDAMIENTO! Se devuelve el error.
13. ¡#DESBORDAMIENTO! – Tabla vs. Arrays dinámicos
No podemos utilizar fórmulas BUSCARX de arrays en las tablas porque no admiten fórmulas de arrays dinámicos.
En su lugar, debe utilizar una función BUSCARX que no sea de matriz y en la que el valor_buscado sea un único valor, no una matriz de valores.
14. #¡DESBORDAMIENTO! – Rango fuera de los límites
¡Si el tamaño de la matriz de salida de una fórmula BUSCARX excede los límites de la hoja (fila y columna), se devuelve el mensaje #DESBORDAMIENTO! Se devuelve en su lugar. (por ejemplo, F3:F < array de columnas completo)
Otros problemas
Hay problemas de BUSCARX que no provocan errores porque no violan ninguno de los criterios.
15. Rango incorrecto
Incluso si los tamaños de las filas del array de búsqueda y del array de retorno son iguales, si sus posiciones no coinciden entre sí, el BUSCARX devolverá un resultado incorrecto.
16. Matriz de valores de búsqueda 1D/2D frente a la matriz de retorno 2D
El valor de búsqueda en XLOOKUP puede ser un array 1D o 2D, lo que convierte a la función XLOOKUP en un array dinámico que se derramará en las celdas adyacentes. Podríamos esperar que una combinación de una matriz de valor de búsqueda 1D (por ejemplo, F3:F4) y una matriz de retorno 2D (por ejemplo, C3:D7) devolverá una salida 2D donde el tamaño de la fila y el tamaño de la columna dependen de la matriz de valor de búsqueda y de la matriz de retorno 2D, respectivamente, pero este no es el caso. El tamaño de la fila seguirá basándose en la matriz de valores de búsqueda 1D, pero el tamaño de la columna será 1, lo que significa que sólo se devolverá la primera columna de la matriz de retorno.
17. Copiando BUSCARX con Referencias Relativas
Si arrastramos una fórmula BUSCARX con referencias relativas, las referencias también se ajustarán respecto a su posición desde la fórmula de referencia, lo que puede llevar a resultados incorrectos.
Si queremos copiar o arrastrar nuestra fórmula BUSCARX a las celdas siguientes, debemos convertir las referencias de la matriz de búsqueda y la matriz de retorno en referencias absolutas. Podemos hacerlo añadiendo el símbolo del dólar delante de la letra de la columna y el número de la fila o pulsando F4 mientras el cursor está en la referencia dentro de la fórmula.
