BúsquedaV con múltiples criterios utilizando VBA y el método de fórmula de matriz.
Entonces, la idea es hacer uso del hermoso Método de Fórmula de Matriz y la idea detrás de él, cuando se necesita hacer un VLookUp con múltiples criterios en VBA.
La pregunta: ¿Podemos traducir esto a VBA?
{=INDEX(rango1, COINCIDIR(1, (A1=rango2)(B1=rango3)(C1=rango4), 0))}
¿Sin usar fórmulas en Excel en absoluto? Por ejemplo, sin hacer esto:
=AGREGAR(15, 6, ‘[TUR Master Report.xlsm]Archivo’!$B$2:$B$13/
((‘[TUR Master Report.xlsm]Archivo’!$B$2:$B$13>=DO2)*
(‘[TUR Master Report.xlsm]Archivo’!$B$2:$B$13<=DP2)*
(‘[TUR Master Report.xlsm]Archivo’!$A$2:$A$13=A2)), 1)
o cualquier cosa similar (.ArrayFormula, .Formula, etc.).
Estaba pensando en algo como esto foo = Coincidir(1, (A1=rangoA)(B1=rangoB)(C1=rangoC), 0), pero por supuesto no funciona, aunque está en la lógica de la fórmula de Excel. Hasta ahora he creado lo siguiente como solución alternativa:
Función GetLookupDataTriple(nombreTabla como String, buscarEnColumna como String, myArray como Variant) como Variant
Dim lo como ListObject
Set lo = Sheet1.ListObjects(nombreTabla)
Dim i como Long
para i = 2 hasta lo.ListColumns(myArray(0)).Range.Rows.Count
si lo.ListColumns(myArray(0)).Range.Cells(RowIndex:=i) = myArray(1) entonces
si lo.ListColumns(myArray(2)).Range.Cells(RowIndex:=i) = myArray(3) entonces
si lo.ListColumns(myArray(4)).Range.Cells(RowIndex:=i) = myArray(5) entonces
GetLookupDataTriple = lo.ListColumns(buscarEnColumna).Range.Cells(RowIndex:=i)
salir de la función
Fin si
Fin si
Fin si
siguiente i
GetLookupDataTriple = -1
Fin función
lo cual funciona bastante bien con 3 filtros, pero la idea es ser un poco más elegante, como en la fórmula original de Excel. Estos son datos de muestra, que hacen funcionar la función de arriba:
?GetLookupDataTriple(“Table1″,”To”,array(“From”,”Bulgaria”,”Cost”,200,”Currency”,”USD”))
davy.ai
Sí, podemos traducir la fórmula de Excel
{=INDEX(rango1,COINCIDIR(1,(A1=rango2)*(B1=rango3)*(C1=rango4),0))}
a VBA. Una forma de hacerlo es usando una fórmula de matriz que aproveche el hecho de que VBA puede manejar matrices directamente. Podemos usar los métodosWorksheetFunction.Index
yWorksheetFunction.Match
en VBA para replicar la lógica de la fórmula de Excel, de esta forma:En este código, primero definimos los valores de búsqueda (A1, B1 y C1) y los rangos de datos (rango1, rango2, rango3 y rango4). Luego creamos dos matrices, una para los valores de búsqueda y otra para los rangos de datos con los que queremos hacer coincidir. Redimensionamos estas matrices al número de filas del rango de búsqueda y llenamos la segunda dimensión de la matriz de coincidencia con los valores de búsqueda.
Luego usamos el método
Evaluate
para comparar la matriz de coincidencia con los rangos de datos utilizando una lógica similar a la fórmula de Excel. La funciónINDEX
devuelve una matriz de números de fila donde se cumplen todas las condiciones, y usamos la funciónMATCH
para encontrar el índice de la primera coincidencia. Por último, usamos nuevamente la funciónINDEX
para devolver el resultado usando el índice y el rango1.Este código debería producir el mismo resultado que la fórmula de Excel, y se puede modificar fácilmente para manejar diferentes rangos y valores de búsqueda.