Custom User Defined Function in place of Index(array(match(lookup,lookup array,0)) ?

[Sense-A]

I want to use a custom formula that only asks for the lookup value.

=INDEX('OrgKeys'!$E$2:$E$5000,MATCH(Lookup!$A8,'OrgKeys'!$A$2:$A$5000,0))

The only value that ever changes is the A8

So, for example, can I have a custom formula such as =orgkey(A8)

I would have to define 'OrgKeys'!$E$2:$E$5000 as the constant table that has the index values I want

I would also have to define 'OrgKeys'!$A$2:$A$5000 as always being the constant array I lookup the value in cell A8 in.

I'd like for the index table and array lookup table to be in a separate workbook on a specific drive in a specific directory that never changes.

It would be even cooler if you could type =orgkey() and by default it assumed you wanted to lookup the value in the cell to the left since that is 100% the case when I use the index(match) formula

Thanks!

 

[James006]

Hello,

You could test the following UDF :

Function OrgKeys(rng As Range)
' example =OrgKeys(A8)
Dim res As Variant
res = Application.Match(rng, Sheets("OrgKeys").Range("A2:A5000"), 0)
  If IsError(res) Then
       OrgKeys = "N/A"
  Else
       OrgKeys = Application.Index(Sheets("OrgKeys").Range("E2:E5000"), res)
  End If
End Function

Hope this will help

 

[Sense-A]

James006,

That works great! Thank you very much. I am going to impress the people in the budget office who will no longer need to look up funds, key descriptions, or account descriptions anymore!

Do you know how I can modify it so that it knows specifically which file (pathname) to look in? I'd like to keep the OrgKeys table in a separate workbook. Will it be able to lookup the data in an external workbook that is closed? Assuming it works like the index((match)) function, it should be able to.

Thanks.

 

[James006]

Glad to hear the UDF is helping you out ...

When it comes to a closed workbook ... am afraid it will not work ....

If you really need to deal with a closed workbook ... you will have to design a totally different approach ...