I have a formula in B:B that looks like this
=IF(ISERROR(MATCH(VLOOKUP(A1,I:J,2,FALSE),'$M:$M,0 )),"Not Found","")

but
the VLOOKUP is standard. it always looks up adjacent cell in I and
returns J
VLOOKUP option always FALSE
MATCH option always 0

I think a VBA function could reduce it to this

=MYFIND(A1,$M:$M)

I know to store a function in a module in personal.xls but and I don't
know how to mimick these 3 formulas.

Any help appreciated.
thanks

Easiest way:

Public Function MyFind(myValue As Variant, myRange As Excel.Range) As
Variant
On Error Resume Next
With Application.WorksheetFunction
Call .Match(.VLookup(myValue, myRange.Parent.Range("$I:$J"), 2,
False), myRange, False)
End With
If Err.Number < 0 Then
MyFind = "Not Found"
Else
MyFind = ""
End If
End Function


On Dec 11, 4:00 pm, Finny388 wrote:
I have a formula in B:B that looks like this
=IF(ISERROR(MATCH(VLOOKUP(A1,I:J,2,FALSE),'$M:$M,0 )),"Not Found","")

but
the VLOOKUP is standard. it always looks up adjacent cell in I and
returns J
VLOOKUP option always FALSE
MATCH option always 0

I think a VBA function could reduce it to this

=MYFIND(A1,$M:$M)

I know to store a function in a module in personal.xls but and I don't
know how to mimick these 3 formulas.

Any help appreciated.
thanks

Note, however, that this function will not update if I:J changes
(because it's not in the argument list), so you have to recalc
manually.


On Dec 11, 5:13 pm, ilia wrote:
Easiest way:

Public Function MyFind(myValue As Variant, myRange As Excel.Range) As
Variant
On Error Resume Next
With Application.WorksheetFunction
Call .Match(.VLookup(myValue, myRange.Parent.Range("$I:$J"), 2,
False), myRange, False)
End With
If Err.Number < 0 Then
MyFind = "Not Found"
Else
MyFind = ""
End If
End Function

On Dec 11, 4:00 pm, Finny388 wrote:



I have a formula in B:B that looks like this
=IF(ISERROR(MATCH(VLOOKUP(A1,I:J,2,FALSE),'$M:$M,0 )),"Not Found","")

but
the VLOOKUP is standard. it always looks up adjacent cell in I and
returns J
VLOOKUP option always FALSE
MATCH option always 0

I think a VBA function could reduce it to this

=MYFIND(A1,$M:$M)

I know to store a function in a module in personal.xls but and I don't
know how to mimick these 3 formulas.

Any help appreciated.
thanks- Hide quoted text -

- Show quoted text -

On Dec 11, 4:14 pm, ilia wrote:
Note, however, that this function will not update if I:J changes
(because it's not in the argument list), so you have to recalc
manually.

On Dec 11, 5:13 pm, ilia wrote:

Easiest way:

Public Function MyFind(myValue As Variant, myRange As Excel.Range) As
Variant
On Error Resume Next
With Application.WorksheetFunction
Call .Match(.VLookup(myValue, myRange.Parent.Range("$I:$J"), 2,
False), myRange, False)
End With
If Err.Number < 0 Then
MyFind = "Not Found"
Else
MyFind = ""
End If
End Function

On Dec 11, 4:00 pm, Finny388 wrote:

I have a formula in B:B that looks like this
=IF(ISERROR(MATCH(VLOOKUP(A1,I:J,2,FALSE),'$M:$M,0 )),"Not Found","")

but
the VLOOKUP is standard. it always looks up adjacent cell in I and
returns J
VLOOKUP option always FALSE
MATCH option always 0

I think a VBA function could reduce it to this

=MYFIND(A1,$M:$M)

I know to store a function in a module in personal.xls but and I don't
know how to mimick these 3 formulas.

Any help appreciated.
thanks- Hide quoted text -

- Show quoted text -

Thanks Ilia works great