通过VBA自定义XLOOKUP函数

hopecode

XLOOKUP函数功能强大，无奈桌面版本都用不了，一时兴起自己动手山寨了一个，主要实现了反向查找、逆序查找和自定义找不到返回值的功能，官方文档中描述的[match_mode]非0的场景感觉太复杂，鉴于没有365试验，先忽略掉了；[search_mode]用二分法那俩参数我也先忽略了，主要能逆向就行。

1. Option Explicit
   
2. 
   
3. Function XLOOKUP(ByRef lookup_value As Variant, ByRef lookup_array As Range, ByRef return_array As Range, Optional ByRef if_not_found As Variant, Optional ByVal match_mode As Integer = 0, Optional ByVal search_mode As Integer = 1)
   
4.     Dim FormulaString As String, separator As String
   
5.    
   
6.     If search_mode = 1 Then
   
7.         If lookup_array.Columns.Count = 1 Then
   
8.             FormulaString = "VLOOKUP("
   
9.             separator = ","
   
10.         ElseIf lookup_array.Rows.Count = 1 Then
    
11.             FormulaString = "HLOOKUP("
    
12.             separator = ";"
    
13.         End If
    
14.         FormulaString = FormulaString & Get_String(lookup_value) & ",if({1" & separator & "0}," & lookup_array.Address(External:=True) & "," & return_array.Address(External:=True) & "),2,0)"
    
15.     ElseIf search_mode = -1 Then
    
16.         FormulaString = "LOOKUP(1,0/(" & lookup_array.Address(External:=True) & "=" & Get_String(lookup_value) & ")," & return_array.Address(External:=True) & ")"
    
17.     End If
    
18.    
    
19.     If Not IsMissing(if_not_found) Then FormulaString = "IFERROR(" & FormulaString & "," & Get_String(if_not_found) & ")"
    
20.     FormulaString = "=" & FormulaString
    
21.    
    
22.     XLOOKUP = Evaluate(FormulaString)
    
23. End Function
    
24. 
    
25. Private Function Get_String(ByRef v As Variant)
    
26.     If TypeName(v) = "Range" Then
    
27.         Get_String = v.Address(External:=True)
    
28.     Else
    
29.         Get_String = """" & v & """"
    
30.     End If
    
31. End Function

复制代码

代码比较粗糙，欢迎批评指正和补充完善！

hopecode

还是用index+match简洁点。。。

1. Function XLOOKUP(ByRef lookup_value As Variant, ByRef lookup_array As Range, ByRef return_array As Range, Optional ByRef if_not_found As Variant, Optional ByVal match_mode As Integer = 0, Optional ByVal search_mode As Integer = 1)
   
2.     Dim FormulaString As String
   
3.    
   
4.     If search_mode = 1 Then
   
5.         FormulaString = "INDEX(" & return_array.Address(External:=True) & ",MATCH(" & Get_String(lookup_value) & "," & lookup_array.Address(External:=True) & ",0))"
   
6.     ElseIf search_mode = -1 Then
   
7.         FormulaString = "LOOKUP(1,0/(" & lookup_array.Address(External:=True) & "=" & Get_String(lookup_value) & ")," & return_array.Address(External:=True) & ")"
   
8.     End If
   
9.    
   
10.     If Not IsMissing(if_not_found) Then FormulaString = "IFERROR(" & FormulaString & "," & Get_String(if_not_found) & ")"
    
11.     FormulaString = "=" & FormulaString
    
12.    
    
13.     XLOOKUP = Evaluate(FormulaString)
    
14. End Function

复制代码

cjc209

hopecode 发表于 2021-1-24 10:53
还是用index+match简洁点。。。

如果光是为了逆向查找 我认为多此一举 lookup本身完全可以代替

aichimalayu

hopecode 发表于 2021-1-24 10:53
还是用index+match简洁点。。。

请问这个Function我导入后，也能主动提示有函数了，可是按规则选好参数后还是提示#NAME?
为何不能执行呢？
放到了XLSB里
谢谢

125675117

hopecode 发表于 2021-1-24 10:53
还是用index+match简洁点。。。

怎么使用的？

GG函数

你可以使用我开发的微软函数，与微软工程师的几乎一模一样