Index Match Function Error Handling

  • Hi all,


    I'm trying to develop a custom function.


    I have a lookup table called 'Shapes' that has two columns:


    1) Column A has a discontinuous range of values ranging from 0.000 to 55.000

    2) Column B has a unique alphanumeric value for that particular row


    None of the values between 0.000 to 55.000 in Column 1 differ by more than 0.003.


    So far, I have the following:


    Code
    1. Function Fetch(v) As String
    2. ' v can be any value between 0.000 and 55.000, including values that may not necessarily be in column A of the lookup table
    3. Fetch = Application.WorksheetFunction.Index(Worksheets("Shapes").Range("B:B"), Application.WorksheetFunction.Match(v) Worksheets("Shapes").Range("A:A"), 0))
    4. End Function

    I'm hoping to be able to tell this function that in instances where the value of 'v' does not match and a '#VALUE!' result is returned to try and search for the value of v + 0.001, and then by v - 0.001, and then by v + 0.002, and then by v - 0.002.


    Is there any way I can modify this function so that a value always results when it is run?


    Any help would be appreciated.


    Cheers,

  • Welcome to the Forum. Please read the Forum Rules to understand how the Forum works and why I have added Code Tags to your post


    All VBA code posted in the forum must be wrapped in code tags, which you omitted, including single-line code snippets.Be sure to use them in future posts.


    How to use code tags


    Just highlight all of the code and press the <> in the post menu above button to add the code tags.


    Thanks.

  • Hi Roy,


    Thank you, will be sure to put in code tags from here on.


    In its current state, the function does what can be done with an INDEX and MATCH functions within excel.


    What I'm hoping to be able to do it, put in a 'Do Until' Loop so that it goes through the values of v - 0.001, v + 0.001, v - 0.002 and v + 0.002, and stops the loop when Len(Fetch) > 0, but not entirely sure how to go about it.


    Shall I upload an Excel spreadsheet to better explain the input and desired output?


    Cheers,

    Paul

  • Quote

    In its current state, the function does what can be done with an INDEX and MATCH functions within excel.

    I don't think it does.


    You can upload an example but I don't think a UDF is necessary.