Search code examples
excelvbanamed-ranges

Simplifing and Index + match function use

I am using the INDEX + MATCH excel function to look for an specific data on that named range. As my "MATCH" column is the second column (column "B"), I need to use the OFFSET function on that.

An excel example formula is this :

=INDEX(Macro;MATCH($F$29;OFFSET(Macro;0;1;;1);0);MATCH(J$2;Macro!$2:$2;0)))

So, to avoid having that function on all over my spreadsheet, I want to create a function to return such data.

I am trying to create the INDEX + MATCH on VBA and it works, by as soon as a try to use the named range (plus the OFFSET), I get some errors.

This gives me error :

DADOSMACRO = Application.WorksheetFunction.Match(Range("DataIWant"), Range("Macro").Offset(, 1), 0)"

This works :

"DADOSMACRO = Application.WorksheetFunction.Match(Range("DataIWant"), Range("Macro!A8:A37").Offset(, 1), 0)"

Full function:

Public Function DADOSMACRO() As String 
    DADOSMACRO = Application.WorksheetFunction.Match(Range("$F$29"), _
                                      Range("Macro").Offset(, 1), 0) 
End Function
Solution

  • Thanks for everybody who tried to help. I finally could solve it with some help from Microsoft support.

    The problem is not regarding using named ranges. The problem is that the named range is a range of whole rows, so the offset VBA function does not work.

    I finally made it work replacing the offset by Application.WorksheetFunction.Match(Range("CELL_I_WANT_FIND"), Range("NAMEDRANGE").Columns(2), 0)

    So the match vba code could find the data on column "B"