Función personalizada Excel XLOOKUP
La función XLOOKUP es exclusiva del programa interno de Office 365.
La función BUSCAR tiene muchas funcionalidades que superan muchas de las debilidades de la función BUSCARV y BUSCARH, pero lamentablemente no está disponible para nosotros por ahora. Pero no se preocupe, podemos crear una función XLOOKUP que funcione exactamente igual que la próxima función XLOOKUP MS Excel. Le añadiremos funcionalidades una a una.
Código VBA de la función XLOOKUP La siguiente función de búsqueda UDF resolverá muchos problemas. Cópielo o descargue el complemento xl a continuación.
Function XLOOKUP(lk As Variant, lCol As Range, rCol As Range) XLOOKUP = WorksheetFunction.Index(rCol, WorksheetFunction.Match(lk, lCol, 0)) End Function
Explicación:
El código anterior es solo INDEX-MATCH básico utilizado en VBA. Esto simplifica muchas de las cosas a las que se enfrenta un nuevo usuario. Si resuelve la complejidad de la función INDEX-MATCH y usa solo tres argumentos. Puede copiarlo en su archivo de Excel o descargar el archivo .xlam a continuación e instalarlo como un complemento de Excel. Si no sabe cómo crear y usar un complemento, `link: / excel-macros-and-vba-add-in-in-vb [haga clic aquí, le ayudará].
XLOOKUP Add-In]
veamos cómo funciona en la hoja de cálculo de Excel.
Sintaxis de XLOOKUP
|
=XLOOKUP(lookup_value, lookup_array, result_array) |
lookup_value: este es el valor que desea buscar en el
matriz_búsqueda.
lookup_array: es un rango unidimensional en el que desea buscar el lookup_value. result_array: también es un rango unidimensional. Este es el rango del que desea recuperar el valor.
Veamos esta función XLOOKUP en acción.
Ejemplos de XLOOKUP:
Aquí, tengo una tabla de datos en Excel. Exploremos algunas funciones usando esta tabla de datos.
Funcionalidad 1.ExactLookup en el lado izquierdo y derecho del valor de búsqueda. Como sabemos, la función BUSCARV de Excel no puede recuperar valores de la izquierda del valor de búsqueda. Para eso, debe usar la compleja combinación INDICE-MATCH. Pero ya no más.
Suponiendo que necesitamos recuperar toda la información disponible en la tabla de algunos números de rollo. En ese caso, también tendrá que recuperar la región, que está a la izquierda de la columna del número de rollo.
Escribe esta fórmula, I2:
|
=XLOOKUP(H2,$B$2:$B$14,$A$2:$A$14) |
Obtenemos el resultado Norte para el rollo número 112. Copie o arrastre hacia abajo la fórmula en las celdas de abajo para llenarlas con las regiones respectivas.
¿Cómo funciona?
El mecanismo es simple. Esta función busca lookup_value en lookup_array y devuelve el índice de una primera coincidencia exacta. Luego usa ese índice para recuperar el valor de result_array. Esta función funciona perfectamente con rangos con nombre.
De manera similar, use esta fórmula para recuperar el valor de cada columna.
Funcionalidad 2. ExactHorizontalLookup encima y debajo del valor de búsqueda. XLOOKUP también funciona como una función HLOOKUP exacta. La función HLOOKUP tiene la misma limitación que VLOOKUP. No puede obtener un valor por encima del valor de búsqueda. Pero XLOOKUP no solo funciona como HLOOKUP, también supera esa debilidad. Veamos cómo.
Hipotéticamente, si desea comparar dos registros. El registro de búsqueda que ya tiene. El registro con el que desea comparar está por encima de lookup_range. Utilice esta fórmula en ese caso.
|
=XLOOKUP(H7,$A$9:$E$9,$A$2:$E$2) |
arrastre hacia abajo la fórmula y tendrá el registro completo de la fila de comparación.
Funcionalidad 3. No es necesario el número de columna y la coincidencia exacta predeterminada.
Cuando utiliza la función BUSCARV, debe indicar el número de columna desde el que desea obtener los valores. Para eso, tienes que contar las columnas o usar algunos trucos, toma la ayuda de otras funciones. Con esta UDF XLOOKUP, no necesita hacer eso.
Si está utilizando BUSCARV para simplemente obtener algún valor de una columna o para verificar si existe un valor en la columna, esta es la mejor solución en mi opinión.
Funcionalidad 4. Reemplaza la función INDEX-MATCH, VLOOKUP, HLOOKUP
Para tareas simples, nuestra función XLOOKUP reemplaza las funciones mencionadas anteriormente.
Limitaciones de XLOOKUP:
Cuando se trata de fórmulas complejas, como //lookup-formulas/vlookup-with-dynamic-col-index.html[VLOOKUP con Dynamic Col Index] `donde VLOOKUP identifica la columna de búsqueda con encabezados, esta XLOOKUP fallará.
Otra limitación es que si tiene que buscar varias columnas o filas aleatorias en la tabla, esta función será inútil ya que tendrá que escribir esta fórmula una y otra vez. Esto se puede solucionar usando link: / excel-range-name-all-about-excel-named-ranges-excel-range-name [named range].
Por ahora, no hemos agregado la funcionalidad aproximada, por lo que, por supuesto, no puede obtener la coincidencia aproximada. Añadiremos eso demasiado pronto.
Si la función XLOOKUP no encuentra el valor de búsqueda, devolverá el error #VALUE no # N / A.
Así que sí, chicos, así es como usan XLOOKUP para recuperar, buscar y validar valores en tablas de Excel. Puede utilizar esta función definida por el usuario para una búsqueda sin problemas a la izquierda o hacia arriba del valor de búsqueda. Si aún tiene alguna duda o algún requisito específico relacionado con esta función o EXCEL 2010/2013/2016/2019/365 o consulta relacionada con VBA, pregúntelo en la sección de comentarios a continuación. Seguramente obtendrá una respuesta.
Artículos relacionados:
link: / custom-functions-in-vba-create-vba-function-to-return-array [Crear función VBA para devolver la matriz] | Para devolver una matriz de una función definida por el usuario, tenemos que declararla cuando nombramos la UDF.
link: / excel-array-formulas-arrays-in-excel-formula [Matrices en Excel Formul] | Aprenda qué son las matrices en excel.
link: / vba-user-defined-function [Cómo crear una función definida por el usuario a través de VBA] | Aprenda a crear funciones definidas por el usuario en Excel `link: / custom-functions-userdefined-functions-from-other-workbooks-using-vba-in-microsoft-excel [Using a User Defined Function (UDF) from another workbook using VBA en Microsoft Excel] `| Utilice la función definida por el usuario en otro libro de Excel `enlace: / custom-functions-return-error-values-from-user-defined-functions-using-vba-in-microsoft-excel [Devolver valores de error desde funciones usando VBA en Microsoft Excel] `| Aprenda cómo puede devolver valores de error desde una función definida por el usuario
Artículos populares:
link: / general-topics-in-vba-split-excel-sheet-into-multiple-files-based-on-column-using-vba [Dividir la hoja de Excel en varios archivos según la columna usando VBA] | Esta hoja de Excel dividida en código VBA se basa en valores únicos en una columna específica.
Descarga el archivo de trabajo.
link: / general-topics-in-vba-turn-off-warning-messages-using-vba-in-microsoft-excel [Desactivar mensajes de advertencia usando VBA en Microsoft Excel 2016] | Para desactivar los mensajes de advertencia que interrumpen el código VBA en ejecución, usamos la clase Application.
link: / files-workbook-and-worksheets-in-vba-add-and-save-new-workbook-using-vba-in-microsoft-excel [Add And Save New Workbook Using VBA In Microsoft Excel 2016] | Para agregar y guardar libros de trabajo usando VBA usamos la clase Workbooks. Workbooks.Add agrega un nuevo libro fácilmente, sin embargo …