Macro Code for replacing Index/Match based on input for "lookup value" and "lookup array"

[IosifFlorin]

Hi,

i am trying to build a macro code for a function that will replace the index/match function (trying to make it simpler.

What i build:

Function FLook(myRange, LookupValue)


test = Application.WorksheetFunction.Index(myRange, Application.WorksheetFunction.Match(LookupValue, ProdCodBandings, 0))


End Function

and how i want to apply it in excel is something like this (having this in a table so referencing @ a cell in the table that will vary depending on the the difference value). Price1SA is a range predefined.

=FLook(Price1SA,[@[Product Code]])


Any idea what to adjust in order to work? Maybe some variables to define?


Thank you!
Iosif

 

[steve the fish]

Heres a function that ive just written which works a bit like index match. You use it like this for example:

=leftlookup(H1,D2:D3,A2:A3)

Where H1 contains the value to find in D2:D3 and A2:A3 is the return range.

Function leftlookup(lookup_value As String, lookup_range As Range, result_range As Range)

With lookup_range
    Set x = .Find(lookup_value, LookIn:=xlValues, LookAt:=xlWhole)
    If Not x Is Nothing Then
        myval = WorksheetFunction.Match(lookup_value, lookup_range)
        leftlookup = WorksheetFunction.Index(result_range, myval)
        Else
        leftlookup = CVErr(xlErrNA)
    End If
End With

End Function