UNA ALTERNATIVA A VLOOKUP

La función VLOOKUP es muy útil cuando se busca información en un rango. En su forma más simple el formato es:

=VLOOKUP(valor buscado, rango donde se busca el valor, el número de columna que contiene el valor que se busca)

En este ejemplo, dado el nombre del producto queremos que nos devuelva el código. Forzosamente la primera columna tiene que contener este dato, y además los datos tienen que estar ordenados de menor a mayor.

Podemos ver que pasa si no están ordenadas, el primer ejemplo devuelve un código incorrecto para "grapes" mientras que el ejemplo de abajo, con sus valores ordenados, devuelve el código correcto.

La función VLOOKALL no tiene estas limitaciones, el valor buscado puede estar en cualquier columna y los valores pueden estar sin ordenar. El formato es:

VLOOKALL(rango donde se busca, valor buscado, número de columna donde se encuentra el valor buscado, número de columna donde se encuentra el valor de interés).

En este caso se ve como el valor buscado es "grapes" (J6) ese valor se buscará en la columna 1, y el código está en la columna 3, el rango donde se buscará es B6:E10. Por lo tanto la fórmula quedaría (en la celda K6):
VLOOKALL(B6:E10,J6,1,3).

Abajo aparece el código para crear esta función. Es necesario que el libro se guarde con formato xlsm y que se agregué un módulo para incluir la función pública (insert → Module).

Public Function VLOOKALL(rng As Range, vref As Variant, colval As Long, colret As Long)
'Carlos Zacarias made me in 3/28/2022
Dim val_found As Variant
Dim val_cell As Variant
Dim DirArray As Variant

'initialize value
val_found = "nf"

DirArray = rng.Value

row_max = UBound(DirArray, 1)
col_max = UBound(DirArray, 2)

i = 1
val_cell = ""
Do Until CStr(val_cell) = CStr(vref) Or i > row_max
val_cell = DirArray(i, colval)
If CStr(val_cell) = CStr(vref) Then
val_found = DirArray(i, colret)
End If
i = i + 1
Loop

VLOOKALL = val_found

End Function