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:

Function Fetch(v) As String


' 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


Fetch = Application.WorksheetFunction.Index(Worksheets("Shapes").Range("B:B"), Application.WorksheetFunction.Match(v) Worksheets("Shapes").Range("A:A"), 0))


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,

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