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 <finny...@yahoo.com> 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 <iasaf...@gmail.com> 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 <finny...@yahoo.com> 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 <iasaf...@gmail.com> 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 <iasaf...@gmail.com> 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 <finny...@yahoo.com> 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